Reference Current Worksheet, Run Macro, Come Back to Current Sheet 1

[shelby55]

Hi

In Excel, setting the zoom control only applies to the worksheet you're working on and not the whole workbook. I created a "zoom all" macro so that all worksheets would show the same size.

In the macro, it runs through all visible sheets but how can I get it to bring focus back to the sheet it started on? For example, if I invoke it on sheet 6 I want it to run and come back to sheet 6 when it ends. Thanks very much.

Sub zoom_choice()
Dim response
Dim canceltest As Variant
Dim x As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False
response = InputBox("Enter the zoom magnification you would like")

If StrPtr(response) = 0 Then
MsgBox "Cancelled", vbOKOnly, "Cancel Zoom"
Else

For Each ws In Worksheets
If ws.Visible Then ws.Activate
ActiveWindow.zoom = response
Next

Application.ScreenUpdating = True
End If
End Sub

 

[PHV]

Like this ?

...
Application.ScreenUpdating = False
response = InputBox("Enter the zoom magnification you would like")
If StrPtr(response) = 0 Then
    MsgBox "Cancelled", vbOKOnly, "Cancel Zoom"
Else
    Dim s As Worksheet: Set s = ActiveSheet
    For Each ws In Worksheets
        If ws.Visible Then ws.Activate
        ActiveWindow.zoom = response
    Next
    s.Activate
End If
Application.ScreenUpdating = True
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Thank you so much, PHV! Works perfectly!!