only_sleeping
New Member
- Joined
- Sep 16, 2022
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
Hi Folks, I'm completely out of my depth whilst trying to write a macro that upon opening a protected and shared Excel file will do the following:
Open to a specific worksheet
Zoom to 100%
Clear any previously saved filters on the opened sheets
Allow filtering on all tabs in the sheet.
I currently have this:
I've cobbled this together from various posts on the internet but not entirely sure what each bit means. Upon opening, I'm getting a "Method 'Unprotect' of object '_Worksheet' failed".
Can anyone help?
Open to a specific worksheet
Zoom to 100%
Clear any previously saved filters on the opened sheets
Allow filtering on all tabs in the sheet.
I currently have this:
VBA Code:
Private Sub Workbook_Open()
Worksheets("Students Data").Activate
ActiveWindow.Zoom = 100
Dim wks As Worksheet
For Each wks In Worksheets
If wks.FilterMode = True Then
wks.UnProtect
wks.ShowAllData
wks.Protect AllowFiltering:=True
End If
Next wks
End Sub
I've cobbled this together from various posts on the internet but not entirely sure what each bit means. Upon opening, I'm getting a "Method 'Unprotect' of object '_Worksheet' failed".
Can anyone help?