Macros on protected sheets

Ramzein,

Please see (and try) the attached file.

If the Invoice sheet (Sheet13) is protected then you need to unlock the sheet before changingthe contents you need cleared.

The extra code in bold below will unlock the Invoice sheet (and re-lock it, allowing only unprotected cells to be entered). For easier use, I've added a string variable myPassword assuming you will use a password to make protection effective. (If not, you could replace myPassword with "" in the code).

The macro to copy data to the Inventory sheet works even if you protect the Invoice sheet.

Sub cleardata()
'
' cleardata Macro
'

'Dim myPassword As String

myPassword = "" 'If a password a*? is used to protect then change to ("a*?")

Sheet13.Activate 'In case macro is called from elsewhere
Sheet13.Unprotect Password:=myPassword 'Unlock sheet

    Range("G47,E20,H20,H34:H43,G34:G43,F34:F43,D34:D43,E49,E54").Select
    Range("E54").Activate
    Selection.ClearContents
    Range("D34").Select

Sheet13.Protect Password:=myPasword, Contents:=True ' Reapply the password here, allowing only unprotected cells to be selected

End Sub

Excel macros are a great way to save time and eliminate errors. If you want to try to run your favorite Excel Macro on a Worksheet that has been protected, with or without a password. When such a Worksheet is encountered your macro may well no longer work and display a Run Time Error.

One way that many do use is like shown below

Sub MyMacro()

Sheet1.Unprotect Password:="Secret"

'YOUR CODE

Sheet1.Protect Password:="Secret"

End Sub

As you can see, the code un-protects Sheet1 with the password "Secret", run the code, and then the password protects it again. This will work but has a number of drawbacks with one being that the code could bug out and stop before it encounters the Sheet1. Protect Password:="Secret" line of code. This of course would mean your Worksheet fully unprotected. The other problem is that you would need a similar code for all Macros and all Worksheets.

Regards,

Rick Bale

The UserInterFaceOnly is an optional argument of the Protect Method that we can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that protected with or without a password.

You need to be fully aware that, If you use the Protect method with the UserInterfaceOnly argument set to True on a Worksheet, then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To set the user UserInterfaceOnly back to True after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

Best,
Jerry M.