Ask Experts Questions for FREE Help !
Ask
    Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #1

    Apr 12, 2009, 03:00 PM
    Lock cell that has data, unlock others
    How to lock only cell that has data, and unlock others while they no data.
    I need solution with: Data-Validation-Settings-Custom-Formula.

    Thanks!
    Sorry for my "english"
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Apr 12, 2009, 11:01 PM

    Tell me a little more about your process. Are you asking how to have a blank cell ready for manual input of data, and once data is inserted manually, the cell becomes locked and cannot be changed?

    This would require a macro to be running on your sheet basically locking cells as data is inserted. You could have this occurring on only certain rows/columns or even ranges of cells. Is this acceptable?

    If so, mock up a sheet showing what you have in mind. Be sure to clearly define which cells should receive this treatment, the name of the sheet or sheets that this should occur on, or clarify that on a sample workbook uploaded here.

    Click on GO ADVANCED and use the paperclip icon to attach a workbook.
    Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #3

    Apr 12, 2009, 11:33 PM
    Quote Originally Posted by jbeaucaire View Post
    tell me a little more about your process. Are you asking how to have a blank cell ready for manual input of data, and once data is inserted manually, ONLY THOSE CELLS becomes locked and cannot be changed?

    this would require a macro to be running on your sheet basically locking cells as data is inserted. You could have this occurring on only certain rows/columns or even ranges of cells. Is this acceptable?

    If so, go ahead and mock up a sheet showing what you have in mind. Be sure to clearly define which cells should receive this treatment, the name of the sheet or sheets that this should occur on, or clarify that on a sample workbook uploaded here.

    Click on go advanced and use the paperclip icon to attach a workbook.
    Try with my workbook

    Thanks again
    Attached Files
  1. File Type: xls Ocevidnik.Odjava.O.Neispravnosti.2009.xls (404.5 KB, 286 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Apr 13, 2009, 12:04 AM

    Please explain the process for entering data in a row. At what moment does the row become locked? What should trigger that moment? Can they edit the data anywhere in the row they are currently working on? Please think this through and decide when/how you want this "lock" to trigger.
    Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #5

    Apr 13, 2009, 12:27 AM
    Quote Originally Posted by JBeaucaire View Post
    Please explain the process for entering data in a row. At what moment does the row become locked? What should trigger that moment? Can they edit the data anywhere in the row they are currently working on? Please think this through and decide when/how you want this "lock" to trigger.

    Sorry for isufficiently data.
    So, What should trigger that moment? - CLICK TO SAVE SPREADSHEET!
    Can they edit the data anywhere in the row they are currently working on?
    - YES, COLUMNS N, O, P, Q, R, S

    Much torture with me, You're very patient man.

    Thanks
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Apr 13, 2009, 02:09 AM

    All right, I added a couple of macros in the ThisWorkbook module. Whenever you try to save this sheet, it will warn you that doing so will make your current changes permanent and give a chance to abort.

    If you say "yes", it will scroll through the sheets 1-20 and color the finished rows blue. I added a "count" column out to the right in column J to accomplish that test.

    If you look in the macro here, you will see the line that protects the sheets. There is another version of that line commented out directly below... it shows you how to add a PASSWORD to the protection so the users cannot simply unprotect the sheet.
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
            Cancel As Boolean)
        a = MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo)
        If a = vbNo Then
            Cancel = True
        Else
            Call UpdateAndSaveSheet
        End If
    End Sub
    
    Private Sub UpdateAndSaveSheet()
    Dim lastrow As Long, i As Integer, ws As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each ws In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", _
                                "9", "10", "11", "12", "13", "14", "15", _
                                "16", "17", "18", "19", "20"))  'extend this array to include all your sheets by name
        ws.Activate
        ws.Protect UserInterfaceOnly:=True
    '    ws.Protect Password:="1234", UserInterfaceOnly:=True
        lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        For i = 9 To lastrow
            If Cells(i, 10).Value = 7 Then
                Rows(i).Locked = True
                Range(Cells(i, 2), Cells(i, 8)).Interior.ColorIndex = 37
            End If
        Next i
    
        If lastrow < 23 Then GoTo FinishLine
    Next ws
    
    FinishLine:
        ThisWorkbook.Save
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    Oh, and I had to remove all those merged cells, those wreak havoc on macros, and they were mostly unnecessary for your layout.
    Attached Files
  3. File Type: xls Ocevidnik.Odjava.O.Neispravnosti.2009-1.xls (381.5 KB, 226 views)
  4. Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #7

    Apr 14, 2009, 01:09 AM
    Thanks for Your time, but I must ask You one more thing.


    So, currently, with Your macro, when I enter data into first row, save workbook,exit, and reopen, ALL cells are locked and no data entry are allowed.

    What I need?

    When I enter data into first row, save workbook,exit, and reopen, all cells EXCEPT first row must be unlocked, when I enter data into second row, save workbook,exit, and reopen, all cells EXCEPT first and second rows must be unlocked... etc.


    Thanks
    Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #8

    Apr 14, 2009, 01:17 AM
    FOR JBeaucaire

    One more thing

    Don't vaste Your time with cells color.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #9

    Apr 14, 2009, 03:30 AM

    No, that's not what's happening. The sheets are locked after you save the first time, but all the yellow cells are still unlocked. Only the blue cells are truly locked and uneditable.

    The different color is sort of critical for seeing clearly which cells can be edited and which cannot.

    I'm sorry, in the sheet I posted earlier I did notice I had accidentally left some of the yellow cells locked from earlier playing with the macro. That's fixed here on this version.

    The sheets 1-20 are protected, but all yellow cells are still editable until after a full row is entered and saved, then it becomes uneditable. All other yellow cells are still accessible without unprotecting.

    Protecting a workbook is the only way to keep users from mucking with cells... even things like fancy conditional formatting tricks and validation restrictions can be easily broken or circumvented, much more easily than a protected worksheet.
    Attached Files
  5. File Type: xls Ocevidnik.Odjava.O.Neispravnosti.2009-2.xls (384.0 KB, 243 views)
  6. Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #10

    Apr 14, 2009, 10:14 AM
    Quote Originally Posted by JBeaucaire View Post
    No, that's not what's happening. The sheets are locked after you save the first time, but all the yellow cells are still unlocked. Only the blue cells are truly locked and uneditable.

    The different color is sort of critical for seeing clearly which cells can be edited and which cannot.

    I'm sorry, in the sheet I posted earlier I did notice I had accidentally left some of the yellow cells locked from earlier playing with the macro. That's fixed here on this version.

    The sheets 1-20 are protected, but all yellow cells are still editable until after a full row is entered and saved, then it becomes uneditable. All other yellow cells are still accessible without unprotecting.

    Protecting a workbook is the only way to keep users from mucking with cells...even things like fancy conditional formatting tricks and validation restrictions can be easily broken or circumvented, much more easily than a protected worksheet.


    Work excellent, BUT... (there's always some BUT)

    "until after a FULL row is entered and saved"

    I need one more thing

    Until after a FULL OR NON-FULL row is entered and saved.


    Thanks
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #11

    Apr 14, 2009, 11:53 PM

    OK, I was sure you had said previously that users should be able to edit incomplete rows, but I guess not.

    All right, I removed the part that was checking to make sure all 7 cells were filled.

    This version will lock individual cells with data in them leaving empty cells still editable.

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
            Cancel As Boolean)
        a = MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo)
        If a = vbNo Then
            Cancel = True
        Else
            Call UpdateAndSaveSheet
        End If
    End Sub
    
    Private Sub UpdateAndSaveSheet()
    Dim lastrow As Long, i As Integer, ws As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each ws In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", _
                                "9", "10", "11", "12", "13", "14", "15", _
                                "16", "17", "18", "19", "20"))  'extend this array to include all your sheets by name
        ws.Activate
        ws.Protect UserInterfaceOnly:=True
    '    ws.Protect Password:="1234", UserInterfaceOnly:=True
        On Error Resume Next
        With Range("B9:H23").SpecialCells(xlCellTypeConstants, 23)
            .Locked = True
            .Interior.ColorIndex = 37
        End With
    Next ws
    
        ThisWorkbook.Save
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    BTW, these sheets are protected, but currently not password protected, so any user can simply choose TOOLS > PROTECTION > UNPROTECT SHEET to unlock the sheet. In my opinion that makes this attempt at security pointless.

    To make it truly secure, you should activate the RED line in the code above by taking out the apostrophe and deactivate/remove the line above. Then put in your password. This will truly protect your sheets from users fiddling.

    And since some users are VBA savvy, you should hide the code as well, probably with the same password. Right-Click on the VBAProject in the left panel of the VBEditor and select VBAProject Properties... use the Protection tab to add a password. Next time you open the sheet all the code will be hidden and you'll need the password to view/edit it.
    Attached Files
  7. File Type: xls Ocevidnik.Odjava.O.Neispravnosti.2009-2.xls (369.5 KB, 230 views)
  8. Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #12

    Apr 15, 2009, 02:11 AM
    Fantastic!
    That's exactly what I needed.
    Thanks very much, JBeaucaire, you'r the greatest.
    :D:D:D:D:
    dasaqr's Avatar
    dasaqr Posts: 5, Reputation: 1
    New Member
     
    #13

    Aug 31, 2009, 11:47 AM

    JBeaucaire,

    You truly are the greatest. I have the same problem as Pajoooo. I have an excel sheet (only one in the workbook) with 10 columns. A1 - J1 are the headers and from A2 we start to input data. Like Pajoo, I want once data is input\, and you hit save for the cell to be locked, and for the rest to be editable. Please can you modify the code you wrote for Pajoooo. As well I already have data up to row 213 in my worksheet. I need to lock all these.

    Thanks in advance.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #14

    Aug 31, 2009, 04:12 PM

    How to use the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. In the VBAProject, doubleclick on the moduleThisWorkbook
    4. Copy and Paste in your code (given below)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The macro is installed and ready to use. When you attempt to save your sheet, it will ask if you want to make your current date permanent, then it will lock all the cells with values in them on Sheet1.

    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If MsgBox("Saving this workbook will lock the entries completed so far and you will no longer be able to edit them. Do you really want to save the workbook now?", vbYesNo) = vbNo Then
            Cancel = True
        Else
            Call UpdateAndSaveSheet
        End If
    End Sub
    
    Private Sub UpdateAndSaveSheet()
    Dim LR As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Sheets("Sheet1").Activate
    LR = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    
    ws.Protect UserInterfaceOnly:=True
    'ws.Protect Password:="1234", UserInterfaceOnly:=True
        
        With Range("A2:J" & LR).SpecialCells(xlCellTypeConstants, 23)
            .Locked = True
            .Interior.ColorIndex = 37
        End With
    
        ThisWorkbook.Save
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    dasaqr's Avatar
    dasaqr Posts: 5, Reputation: 1
    New Member
     
    #15

    Sep 1, 2009, 12:02 AM

    Thanks JB, I truly appreciate it.

    However when I tried to run its giving me an error.It says was not defined and when I try to define was as Worksheet on the same line as LR as Long. I get a syntax error on the line Private Sub UpdateAndSaveSheet().

    Please help.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #16

    Sep 1, 2009, 04:29 AM

    Change ws. to ActiveSheet.
    dasaqr's Avatar
    dasaqr Posts: 5, Reputation: 1
    New Member
     
    #17

    Sep 1, 2009, 08:34 AM

    JB thanks again. This seems to work, all the filled cells turn blue and unfilled yellow, but the yellow ones are also uneditable, I have to unprotect sheet to edit them.

    I need the unfilled cells to be editable without having to unprotect the sheet.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #18

    Sep 2, 2009, 02:41 PM

    TEACHER'S HAT = ON:

    When you look at the macro, does any particular line of the code jump out to you as the line that is locking the cells?
    dasaqr's Avatar
    dasaqr Posts: 5, Reputation: 1
    New Member
     
    #19

    Sep 2, 2009, 09:33 PM

    With Range("A2:J" & LR).SpecialCells(xlCellTypeConstants, 23)
    .Locked = True

    JB this looks to be the line.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #20

    Sep 2, 2009, 11:29 PM

    So if you don't want them locked... try removing the one line referring to locking.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How can I unlock the control lock on my Kenmore washer? [ 18 Answers ]

How can I unlock the control lock on my Ken,ore washer?

Thinkpad - NBRs Lock /Fn key - won't unlock [ 6 Answers ]

I've tried unlocking the numbers lock on my IBM thinkpad and still, when I type, I get numbers instead of letters. Per a suggestion on this site I pressed the Fn key and nbrs key and that didn't work. Any other ideas?

Subsidy unlock codes for motorola cell phones [ 1 Answers ]

If anyone knows how to unlock subsidy codes for motorola rzors can you please help me it would b greatly appreciated.

Loading different data into a cell [ 2 Answers ]

Hello everyone, I'm looking for a way to load different data into a cell, or part of the page on the click of a link. So I've got a load of links across the top of the page, and when each is clicked, different data is displayed (ideally more HTML) in the rest of the page, without the main...

How can I unlock the control lock on my Kenmore washer? [ 1 Answers ]

How can I unlock the control lock on my Kenmore washer?


View more questions Search