Selected Answer
I recommend a procedure like this one.
Sub SetProtection(Ws As Worksheet, _
ByVal Pw As String, _
ByVal LockMode As Boolean)
' 17 Oct 2017
With Ws
If .ProtectContents <> LockMode Then
If LockMode Then
.Protect Password:=Pw, _
DrawingObjects:=True, _
Contents:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True
' there are many more properties you can set
.EnableSelection = xlNoRestrictions
Else
.Unprotect Pw
End If
End If
End With
End Sub
It should be located in a standard code module. In the attached workbook I called the module 'Utilities'. Observe that you can specify many properties of the protection object, both more and different ones than included in my code. Call the sub with a line of code as below from anywhere in your workbook.
SetProtection Sheet1, Pw, True
The call has 3 arguments. The first is a worksheet object, specifying the worksheet to be [un]protected. The second one is the password. The last one can be either True (to protect the sheet) or False (to unprotect it). In the attached workbook the call is made from a Workbook_Open event procedure which must be in the 'ThisWorkbook' code module.
Now you can unprotect the sheet, run your macro and protect your sheet again. The call to unprotect might be at the beginning of your macro and its last line might be a call to reinstate protection.
BTW, I recommend that you check the checkbox "Require variable declaration" in VBE/Tools/Options -> Editor. If you do, VBA will add "Option Explicit" at the top of each code sheet automatically (you can also add it manually) which will let VBA point out to you whenever you forgot to declare a variable, mis-declared it or typed a variable name wrong. I promise you, if that won't prolong your life in absolute terms then the useful and enjoyable part of it. :-).