VB .Protect operates differently after closing and reopening workbook

[crackod93]

Hello,

Im using the following VB code to protect the active worksheet and which enables outlining and filters to continue to work, however when i close the workbook and reopen it, the protect mode is still on, but the ability to use the filters and outlining has suddenly disabled,
Any ideas??

Code:

Sub Protect_ActiveSheet()

On Error Resume Next

With ActiveSheet
.Protect Password:="lock", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True

End With

ActiveSheet.Outline.ShowLevels ColumnLevels:=1

Range("D12").Select

End Sub

 

[Richard Schollar]

UserInterfaceOnly only lasts for the duration of the current Excel session (ie until you close the workbook) - you would need to pass that code again on opening the file up to get it to work again

 

[crackod93]

Hi Richard,

Thanks for your quick response on this, would i be correct in assuming that i could just enter this 'userinterfaceonly' command under a 'workbook_open' sub, or would i need to pass all the commands again for the desired sheet?

reason for asking this is that this protection is only set on certain worksheets which i will not always know the name of the sheet or sheets.

Is there a more suitable command which could be used to permanently set this protection on the sheet with these properties, so even when closing and reopening this would still work?

thanks

 

[Richard Schollar]

Just the Protect line on the relevant worksheets - everything else should remain the same. If you find you do need to pass everything again, I would just call the sub from within the Workbook_Open event procedure itself eg:

Private Sub Workbook_Open()
Protect_Activesheet   'make sure correct sheet is active first of course (or change code to work on specific sheet
End Sub

 

[crackod93]

hi, thanks for the quick response.

the issue is that i have no control over what the sheet name or names would be, there could be multiple sheets.

is there any such 'workbook_open' available for a sheet when its selected? i.e. when you go to the sheet it automatically runs the protect script?

alternatively, i would know what the sheet would not be called, so is there a way to say run this protection on all sheets except sheet name A, B or C, but okay for any other sheets it finds in the workbook?

thanks

 

[Richard Schollar]

You could look at the worksheet_activate event, or use the sheet's codename rather than its physical name.

You could loop thru all worksheets on open like this:

For Each ws In Worksheets
  Select Case ws.Name
    Case "Sheet A","Sheet B","Sheet C", "Sheet D"  'all sheets you want to exlcude
    Case Else
         ws.Protect UserInterFaceOnly:=True
  End Select
Next ws

 

[crackod93]

excellent! this works a treat, thank you ever so much!!!!

The final code im using is:

Private Sub workbook_open()

On Error Resume Next

For Each ws In Worksheets
Select Case ws.Name
Case "Sheet A", "Sheet B", "Sheet C"
Case Else
ws.Protect Password:="lock", userinterfaceonly:=True
ws.EnableOutlining = True
ws.EnableAutoFilter = True
ws.Outline.ShowLevels ColumnLevels:=1

Range("D12").Select
Range("A12").Select

End Select

Next ws

Sheets("Sheet A").Select
Range("A1").Select

End Sub