How to Automatically Protect Worksheet After Macro Runs?

[AAF]

Hi all,
I’ve created a macro that performs some calculations and updates a report sheet. I want to automatically protect the worksheet after the macro finishes running to prevent accidental edits as it is a shared document.


What’s the best way to do this in VBA? Also, how do I allow users to still select cells but not change anything?


Appreciate any help or code examples!

[June7]

Protecting a sheet does nothing if cells are not set as Locked.

First, cells you want to restrict must have Locked set. Then turn Protection on or off.

Allowing selection of locked or unlocked cells is a feature of Protection. Right click on sheet tab then click ProtectSheet to view options.

A locked cell is read only when Protection is on. It can be selected but attempting to type in it will trigger message.

Code to protect/unprotect is simple.

Just Protect or Unprotect will act on sheet that is active. Prefix with sheet name if you want to specify.

Worksheets("sheetname").Protect

However, there is nothing preventing any user to right click > unprotect unless you want to encrypt with password.

By "shared" do you mean all user edits are supposed to be saved to same file?

[Aussiebear]

Maybe this as an additional method that offered by June7

Sub YourMacro()
....

' Protect the active worksheet
ActiveSheet.Protect Contents:=True, Scenarios:=True, UserInterfaceOnly:=False
End Sub

[Aussiebear]

And just in case you need to unprotect the worksheet to run the macro.

Sub MyExistingMacro()
    Const ProtectionPassword As String = "dunno"
    ' --- Start of your existing macro ---
    ' Unprotect the sheet at the beginning if it might be protected
    ' This is only needed if your macro will make changes AND you didn't use UserInterfaceOnly:=True when protecting previously
    On Error Resume Next 
' Handles case where sheet might not be protected
    ActiveSheet.Unprotect Password:=ProtectionPassword
    On Error GoTo 0 
     ' Resumes normal error handling
    ' Your existing macro code goes here
    Range("A1").Value = "Macro Ran!"
    ' ... more code that modifies the sheet ...
    ' Re-protect the sheet at the end
    ActiveSheet.Protect Password:=ProtectionPassword, Contents:=True, UserInterfaceOnly:=True
End Sub