Get macros to work on protected sheet

**howsitgoing** · 01-10-2012, 04:04 PM

Hi

I have a data file with various added buttons with some very basic macros attached to the buttons to help me search & filter quickly , but i would like to protect the worksheet , but my macros are then disabled when i protect the worksheet is there away round this?

cheers
barry

**JBeaucaire** · 01-10-2012, 06:24 PM

In your macros you "Unprotect the sheet, run the code, protect the sheet again".

Or

Use a macro to protect your sheets in the first place and add the UserInterfaceOnly:=True to the protection parameters so that VBA is inherently allowed to keep working.

**howsitgoing** · 01-11-2012, 04:33 PM

hi

I have added a macro using the record function to protect the sheet with-out problem (thanks) , but any tips for where i place the "UserInterfaceOnly:=True" into the macro
thanks
barry

Sub Macro6()
'
' Macro6 Macro
' protect worksheet
'

'
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFiltering:=True
End Sub

**JBeaucaire** · 01-11-2012, 06:16 PM

No, just add it anywhere:

Sub ProtectSheet()
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, _
          AllowFiltering:=True, UserInterFaceOnly:=True
End Sub

Thing to remember is that this parameter will not persist when the workbook is closed. The sheet stays protected, but this parameter is lost. When you reopen the sheet, you will need to reset the protection to activate that flag again for that session. Then your macros will keep working.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.