Forum Discussion

Copper Contributor

Jan 27, 2024

Solved

Macro that unlocks a sheet - then locks it when complete

Within a macro I would like to unlock a password protected sheet, copy & paste updates from another sheet, and password protect the sheet again within the macro. I found some simple code that unlocks...

Macros and VBA

HansVogelaar

Jan 27, 2024

RMills7676

It could look like this:

Sub UpdateData()
    Const PW = "secret" ' the password of the protected sheet
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet ' the protected sheet
    Application.ScreenUpdating = False
    ' Change the names as needed
    Set SourceSheet = Worksheets("Source")
    Set TargetSheet = Worksheets("Target")
    TargetSheet.Unprotect Password:=PW
    ' Your code here, for example
    SourceSheet.Range("A1").Copy Destination:=TargetSheet.Range("B1")
    TargetSheet.Protect Password:=PW
    Application.ScreenUpdating = True
End Sub

HansVogelaar

MVP

Jan 27, 2024

RMills7676

It could look like this:

Sub UpdateData()
    Const PW = "secret" ' the password of the protected sheet
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet ' the protected sheet
    Application.ScreenUpdating = False
    ' Change the names as needed
    Set SourceSheet = Worksheets("Source")
    Set TargetSheet = Worksheets("Target")
    TargetSheet.Unprotect Password:=PW
    ' Your code here, for example
    SourceSheet.Range("A1").Copy Destination:=TargetSheet.Range("B1")
    TargetSheet.Protect Password:=PW
    Application.ScreenUpdating = True
End Sub

RMills7676
Copper Contributor
Jan 27, 2024
Thats a little too complicated for me, I only do macros by recording my steps. I recall years ago if I pressed unlock sheet and put in the password while I was recording a macro, and locked it again with the same password at the end it would work. but that does not seem to be working anymore.