Lock edited cells with Macro Button

tlc53

Active Member
Joined
Jul 26, 2018
Messages
334
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
Your code would look something like this:
Code:
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.
 
Last edited:

tlc53

Active Member
Joined
Jul 26, 2018
Messages
334
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 26, 2018
Messages
334
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 26, 2018
Messages
334
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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:
Code:
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

Active Member
Joined
Jul 26, 2018
Messages
334
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! :)
 

Forum statistics

Threads
1,082,385
Messages
5,365,151
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top