Lock edited cells with Macro Button

[tlc53]

Hi there,

I have found instruction on how to lock cells immediately after data has been entered however, I'd like to lock the cells with a push of a button/macro. Is this possible?

Data is currently entered in B12:K105. I would like it so when they hit the "Transfer Journal" button, any cells they have added data to in B12:K105 then becomes locked however, blank cells are editable/can still have data entered later.

Thanks for you time!

 

[Joe4]

I have found instruction on how to lock cells immediately after data has been entered however, I'd like to lock the cells with a push of a button/macro. Is this possible?

Are you just locking (which by itself, doesn't do much), or also adding password protection?
Can you post the code your currently have for that, and we can help you convert it to a manual macro instead of an automated one?

 

[Joe4]

Your code would look something like this:

Sub MyProtectMacro()

    Dim cell As Range
    
    Application.ScreenUpdating = False

'   Unprotect activesheet (add password, if necessary)
    ActiveSheet.Unprotect
    
'   Loop through all cells
    For Each cell In Range("B12:K105")
'       Lock if cell value is not blank
        If Len(cell) > 0 Then
            cell.Locked = True
'       Otherwise, unblock cell
        Else
            cell.Locked = False
        End If
    Next cell
    
'   Reprotect sheet (add password, if necessary)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    Application.ScreenUpdating = True
    
End Sub

The only thing you may need to change is if you are using a password in your sheet protection.
To see what that code would look like, simply use your Macro Recorder, and record yourself protecting and unprotecting your sheet with the password, and see what the code looks like, and make the necessary edits to those two lines of code.

 

[tlc53]

Hi Joe. Thanks for this.
I tried recording a macro with me unprotecting and protecting the sheet with a password but it didn't record the password in the VBA code.
I tried two different codes (see below) but they are both returning error code 400.
The sheet is Sheet6 but has been renamed O2.

Sub MyProtectMacro()

Dim cell As Range

Application.ScreenUpdating = False

'Unprotect a worksheet with a password
Sheets("O2").Unprotect Password:="password"

' Loop through all cells
For Each cell In Range("B12:K105")
' Lock if cell value is not blank
If Len(cell) > 0 Then
cell.Locked = True
' Otherwise, unblock cell
Else
cell.Locked = False
End If
Next cell

'Protect worksheet with a password
Sheets("O2").Protect Password:="password"

Application.ScreenUpdating = True

End Sub

This is the other one I tried..

Sub MyProtectMacro()

Dim cell As Range

Application.ScreenUpdating = False

'Enable changes to worksheet by VBA code, even if protected
Sheets("O2").Protect Password:="password", _
UserInterfaceOnly:=True

' Loop through all cells
For Each cell In Range("B12:K105")
' Lock if cell value is not blank
If Len(cell) > 0 Then
cell.Locked = True
' Otherwise, unblock cell
Else
cell.Locked = False
End If
Next cell

Application.ScreenUpdating = True

End Sub


I'm not very confident yet with my coding so I am fudging it a little bit

 

[Joe4]

Are you on the "O2" sheet when you call this macro?
If you hit "Debug" when you get that error, which line of code does it highlight?

 

[tlc53]

Yes, I have created a macro button on the "O2" sheet for the VBA code to run.
It doesn't give me a debug option where it takes me to the line which is causing the problem. It just has a big red/white cross stating error 400.
If I view the code and press F5 it comes up with "Run-time Error 1004 Application-defined or object-defined error"

I tried removing all aspects of the unprotect/protect part of the code but that doesn't seem to help at all. I was hoping to see if the lock/unlock cells part of the code was working but I just had the same errors come up.

Any other suggestions on where I might be going wrong? Thank you

 

[Joe4]

Are you sure that you have the sheet name correct? I often mix up the number 0 and the capital letter O.
The other thing to try is to step into your code and go through it one line at a time (using the F8) until the error occurs.
That should identify the offending row.

 

[tlc53]

Yes, I definitely put O2 and not 02 however, I tried removing the unprotect/protect part of the code as below (so no sheet reference in code). I figure if I get the lock/unlock cells to work first, I can work on that side later. Here is the code I put in but same errors as mentioned..

Sub MyProtectMacro()


Dim cell As Range


Application.ScreenUpdating = False


' Loop through all cells
For Each cell In Range("B12:K105")
' Lock if cell value is not blank
If Len(cell) > 0 Then
cell.Locked = True
' Otherwise, unblock cell
Else
cell.Locked = False
End If
Next cell


Application.ScreenUpdating = True


End Sub

I tried the debugging/F8 function. Not sure exactly what I'm looking for. It lights row 1 of code (as above), then row 3, 5, 7, 8, 12, 13, 7, 10, 11, 12, 13, (then repeats pattern 7, 10, 11, 12, 13).

Hmmm...

 

[Joe4]

I tried the debugging/F8 function. Not sure exactly what I'm looking for. It lights row 1 of code (as above), then row 3, 5, 7, 8, 12, 13, 7, 10, 11, 12, 13, (then repeats pattern 7, 10, 11, 12, 13).

Yes, you could keep doing that until you get an error, and then see what line/row you are on (see you are looping through a lot of cells, that could take a while).

Might you have an error in the rnage B12:K105?
Or perhaps any merged cells in this range?

This might help identify where the issue is:

Sub MyProtectMacro()

Dim cell As Range

Application.ScreenUpdating = False

On Error GoTo err_check
' Loop through all cells
For Each cell In Range("B12:K105")
' Lock if cell value is not blank
    If Len(cell) > 0 Then
        cell.Locked = True
' Otherwise, unblock cell
    Else
        cell.Locked = False
    End If
Next cell
On Error GoTo 0

Application.ScreenUpdating = True

Exit Sub


err_check:
    MsgBox "Error is in cell: " & cell.Address
    
Application.ScreenUpdating = True

End Sub

This should return a message box telling you the offending cell.

 

[tlc53]

Happy new year!
Thanks for your last post. As it happens, I had one column of merged cells and once I unmerged them, everything started to work as expected.
Appreciate all your help! Thanks again!