VBA to change zoom of all Worksheets in a workbook Excel 2019 for Mac

[SanjayGMusafir]

Hi Experts,
Here comes a new problem. With Excel 2019 for Mac, when you open a file, it doesn't display Customised Macro Buttons.

The workaround is to change the zoom level and they all magically appear.

So I wrote a code to change zoom level of all worksheets in one click. But it seems to be not working. It only changes zoom level of one active window and doesn't move to another sheets. There must be some technical error.

Please help

Sub ZoomAll()
'
' ZoomAll Macro
'


' To change zoom value of all windows


    Dim wsheet As Worksheet
    
    For Each wsheet In ActiveWorkbook.Worksheets
    ActiveWindow.Zoom = 150
    ActiveWindow.Zoom = 135
    Next wsheet
    
    ActiveWorkbook.Save
    
End Sub

 

[My Aswer Is This]

Only the active sheet can be zoomed.

So use this script.
When you activate the sheet the zoom will take place
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
In upper left corner of screen double click on This Workbook
Paste the code in the VBA edit window

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  10/17/2018  10:22:33 AM  EDT
ActiveWindow.Zoom = 150
End Sub

This works on Excel 2013 for PC
Not sure about 2019 for Mac
Try it and see

 

[My Aswer Is This]

Or do it this way:

But then you would have to do it again every time you add a new sheet.

Sub Zoom_Me()
'Modified 10/17/2018 10:31 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        ActiveWindow.Zoom = 150
    Next
Application.ScreenUpdating = True
End Sub

 

[SanjayGMusafir]

@My Aswer Is This

I need to modify my code where it makes every sheet active by itself and changes the zoom level.

Only the active sheet can be zoomed.

So use this script.
When you activate the sheet the zoom will take place
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
In upper left corner of screen double click on This Workbook
Paste the code in the VBA edit window

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  10/17/2018  10:22:33 AM  EDT
ActiveWindow.Zoom = 150
End Sub

This works on Excel 2013 for PC
Not sure about 2019 for Mac
Try it and see

 

[My Aswer Is This]

Not sure what your now asking for.
Did you try my second post.

And why do you have:
Zoom 150
And then:
The Zoom 135

 

[My Aswer Is This]

Glad you see my two ways.
And you choose which way you want.

 

[SanjayGMusafir]

@My Aswer Is This

This code worked for me. Thanks

Or do it this way:

But then you would have to do it again every time you add a new sheet.

Sub Zoom_Me()
'Modified 10/17/2018 10:31 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        ActiveWindow.Zoom = 150
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

@My Aswer Is This

This code worked for me. Thanks