Home » Blogs » Vishesh's blog

Sheet Protect (User Interface only) using VBA

Vishesh's picture
Submitted by Vishesh on 19 April, 2011 - 14:31
Generally, if you want your vba code to run on a protected sheet, you wrap your code in unprotect/protect statements in every procedure or function. You also have to remember to protect the sheet in case it is unprotected. Here is the attached file that clearly explains how you can avoid this. The following code in the Workbook_Open procedure/event protects all the sheets from the user but allows the macro code to work on the sheets. 
Private Sub Workbook_Open()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        wks.Protect Password:="abc", UserInterfaceOnly:=True
    Next wks
End Sub
There is no command for protection used anywhere in the workbook other than used in the Workbook_Open procedure as above. The code behind the two buttons on the sheet doesn't protect or unprotect the sheet for the respective codes to run. Download the attached excel file and see how this works.
AttachmentSize
SheetProtectionUserInterface.xls32 KB
»