Apply Zoom VBA to Whole Workbook

[SaraWitch]

Hello peeps!

Does anyone know how to apply the following formula to the whole workbook? (I've tried ActiveWorkbook but that didn't work!)

Private Sub Workbook_Open()
ActiveWindow.Zoom = 100
End Sub

Ta very muchly!

 

[RoryA]

You have to activate each sheet in turn:

Private Sub Workbook_Open()
   Application.screenupdating = False
    For Each sh In ThisWorkbook.Sheets
        sh.Activate
        ActiveWindow.Zoom = 100
    Next sh
    Application.screenupdating = True
End Sub

 

[SaraWitch]

Ah, thank you, Rory.

Whilst this does return view to 100%, I already have several actions in my Workbook_Open and when I add this code it disables other formulas (mainly open workbook to chosen sheet (second code)) and unhides a hidden sheet (which I don't want unhidden)...?

Private Sub Workbook_Open()
MsgBox "My text", vbOKOnly + vbExclamation, "Workbook etiquette"

Worksheets("Sheet1").Activate

Worksheets("Sheet2").Columns("A:EN").Hidden = False

Application.screenupdating = False
    For Each sh In ThisWorkbook.Sheets
        sh.Activate
        ActiveWindow.Zoom = 100
    Next sh
Application.screenupdating = True

Dim wks As Worksheet
Dim MyPassword As String

MyPassword = "My password"

For Each wks In Worksheets

    If wks.FilterMode = True Then
          wks.Unprotect password:=MyPassword
          wks.ShowAllData
          wks.Protect password:=MyPassword, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
    End If
Next wks

End Sub

 

[RoryA]

It would have been a good idea to lead with the fact that you already had a load of code there!

The code I posted should not unhide anything - you should get an error if the sheet is hidden because you can't activate it. Try something like this:

Private Sub Workbook_Open()
   Const MyPassword As String = "My password"
   MsgBox "My text", vbOKOnly + vbExclamation, "Workbook etiquette"
   
   Worksheets("Sheet2").Columns("A:EN").Hidden = False
   
   Application.ScreenUpdating = False
   Dim wks As Worksheet
   For Each wks In ThisWorkbook.Worksheets
      If wks.Visible = xlSheetVisible Then
         wks.Activate
         ActiveWindow.Zoom = 100
      End If
      If wks.FilterMode = True Then
         wks.Unprotect Password:=MyPassword
         wks.ShowAllData
         wks.Protect Password:=MyPassword, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
      End If
   Next wks
   Application.ScreenUpdating = True
   
   Worksheets("Sheet1").Activate

End Sub

 

[SaraWitch]

Thanks, Rory, and, sorry! My bad! Thought I could just add it in!


This new code is not working for me though. I'm getting "Run-timer error '1004':

Unable to set the Hidden property of the Range class"

 

[SaraWitch]

Oh, and the zoom is reverting to 100% when the sheet is left. I would like it to just revert when the workbook is left (otherwise would be really annoying for someone working!), if possible of course!

Scrap that! That's from the previous code which is elsewhere (my original code)!

 

[RoryA]

Is Sheet2 protected?

 

[SaraWitch]

Yes, Sheet2 is protected. There are six sheets in all. Four have the same password, two have a different one. One sheet is hidden. The workbook is currently a legacy share but will shortly be moved to OneDrive to allow all functionality (e.g., anything to do with unprotecting a sheet doesn't work in legacy share).

And I didn't realise that the original code works on all sheets when you select another workbook sheet and go back!

So, this would work, albeit a bit annoying for those that are changing the view; so, I wonder if there's a way to revert to 100% every time the workbook is closed/opened, rather than when a sheet is exited...?

This time, I'll give you all my ThisWorkbook codes!

'Global Variable to hold last sheet selected
Public lastSheet As Object
Public PrevActiveCell As Range
Dim CurActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set PrevActiveCell = CurActiveCell
    Set CurActiveCell = ActiveCell
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Zoom = 100
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set lastSheet = Sh
End Sub

Private Sub Workbook_Open()
 MsgBox "If the scroll bars lock (i.e., you can't move around the worksheet via your keyboard), 'Unfreeze Panes' in the 'View' tab - the panes will automatically re-freeze when you start to work." & vbNewLine & " " & vbNewLine & "When hiding columns, make sure you first select a cell in the column you want to hide. If you do not, it may hide a different column!" & vbNewLine & " " & vbNewLine & "Please also note that the fill handle has been disabled in this workbook to protect the integrety of formulas and formatting.  And you will only be able to paste values (ctrl v)." & vbNewLine & " " & vbNewLine & "Read the instructions for more helpful hints and tips." & vbNewLine & " " & vbNewLine & "Please take care when working with data to make sure that it is correct - data management is everyone's responsibility!" & vbNewLine & " " & vbNewLine & "Thank you!", vbOKOnly + vbExclamation, "Workbook etiquette"

 Worksheets("Please Note!").Activate
 
 Worksheets("Sheet1").Columns("A:EN").Hidden = False
 
    Dim wks As Worksheet
    Dim MyPassword As String
  
    MyPassword = "PASSWORD"
  
    For Each wks In Worksheets
  
        If wks.FilterMode = True Then
            wks.Unprotect password:=MyPassword
            wks.ShowAllData
            wks.Protect password:=MyPassword, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
        End If
    Next wks

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
 Worksheets("Sheet1").Columns("A:EN").Hidden = False
 
    Dim wks As Worksheet
    Dim MyPassword As String
  
    MyPassword = "PASSWORD"
  
    For Each wks In Worksheets
  
        If wks.FilterMode = True Then
            wks.Unprotect password:=MyPassword
            wks.ShowAllData
            wks.Protect password:=MyPassword, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
        End If
    Next wks
 
End Sub

Sub FindLastCell()
    Dim LastRow As Long
    Dim LastColumn As Long
    LastRow = Cells(Rows.Count, 1).End(c1Up).Row
    LastColumn = Cells(1, Columns.Count).End(x1ToLeft).Column
End Sub

If not, I'll leave as is...

 

[RoryA]

The Workbook_Open code only runs when the workbook is opened. It will not run when switching between sheets. So any code that you only want to run when the workbook is opened should go in there and nowhere else. So remove this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Zoom = 100
End Sub

If Sheet2 is protected without the option to allow formatting columns, then you need to unprotect it in the code before resizing them.

 

[SaraWitch]

Yeah, I did remove it when putting in the new code, and formatting columns is allowed in all protected sheets. I've redone it, just in case, but am now getting this error:

Run-time error '1004':
Method 'Activate' of object'_Worksheet' failed