Need VBA code to unlock individual sheets

K

kek_382

New member
Hi, because I have the unlock function in the ribbon disabled after compile, I need code for users to be able to unlock certain sheets for editing. I would want the code to be executed through a form control I place on the page. Then to re-lock the sheet once the workbook is closed. Can someone provide this for me please, I’m not good at VBA.
Thanks.
 
Below is one way to do this. In this example, assume that you want to unlock (and later re‑lock) sheets named Sheet1 and Sheet2. You can adjust the sheet names and password as needed.

1. Create a Macro to Unlock the Sheets​

Place the following code in a standard module (for example, in Module1):
Code: 
Sub UnlockSheets()
    Dim ws As Worksheet
    Dim pwd As String
    
    ' Change this to your chosen password
    pwd = "YourPassword"
    
    ' Loop through each worksheet and unlock only the ones you want.
    ' (Adjust the names in the If statement as needed.)
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
            ws.Unprotect Password:=pwd
        End If
    Next ws
End Sub
How to use this macro:
  • Insert a Form Control Button on your worksheet.
  • Right‑click the button, choose Assign Macro…, and select UnlockSheets.
  • When users click the button, the specified sheets will be unprotected so they can edit them.

2. Re‑lock the Sheets When the Workbook Closes​

To ensure that the sheets are locked again when the workbook closes, use the Workbook_BeforeClose event. Place this code in the ThisWorkbook module:
  1. In the VBA Editor, double‑click ThisWorkbook in the Project Explorer.
  2. Paste in the following code:
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim pwd As String
    
    pwd = "YourPassword"  ' Use the same password as above
    
    ' Loop through each worksheet and lock only the ones you want.
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
            ws.Protect Password:=pwd
        End If
    Next ws
End Sub
If you need to allow the user to input a password to unlock the sheets, you can modify the UnlockSheets macro to include an InputBox prompt. For example:
Code: 
Sub UnlockSheetsWithPrompt()
    Dim ws As Worksheet
    Dim pwdEntered As String
    
    pwdEntered = InputBox("Enter the password to unlock the sheets:")
    
    ' Replace "YourPassword" with your actual password for comparison.
    If pwdEntered = "YourPassword" Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
                ws.Unprotect Password:="YourPassword"
            End If
        Next ws
    Else
        MsgBox "Incorrect password!", vbExclamation
    End If
End Sub
 
Hi and thanks, but it is not working for me. Please see screenshots.
I have done exactly as instructed. Plus I went into the macro security settings and allowed everything but still the same result.
Screenshot 2025-02-10 101116

Screenshot 2025-02-10 100828
 
It’s not just the macro settings you have to check.
With file closed, right click->Properties, There should be an Unblock checkbox, file might be downloaded from web so it might have the MOTW.
 
Yep, checked there first, attached is what was found.
Screenshot 2025-02-11 064935

And if it makes a difference, I am on Win 10/64, Excel 365.
 
Last edited:
Not sure what you mean? Can you please explain in more detail?
I do have several files that I want to use this in. And am using copies to test it with.
 
I have just had another go at it. This time no security issues.
But it is not working. It’s not protecting the sheets on exiting the workbook. So, when I open it, they are already unlocked. Can you check the code works at your end please?
 
You must log in or register to reply here.
Back
Top