setting all sheets in workbook to same zoom level?

I'd like to add a button to my application that sets all the sheet zooms to
a zoom level as specified by the user. The below works, but is there a
simpler way?

Sub Zoom()
Dim WS As Worksheet, OrgWS As Worksheet
Application.ScreenUpdating = False
Set OrgWS = ActiveSheet
For Each WS In Application.Worksheets
WS.Select
ActiveWindow.Zoom = 85
Next WS
OrgWS.Select
End Sub

The above would have problems with hidden sheets. So to work properly it
would have to be modified to detect them, then to rehide them.

Don <www.donwiss.com> (e-mail link at home page bottom).

Hi Don,
Try this:
Sub Zoom()
Dim WS As Worksheet, OrgWS As Worksheet
Application.ScreenUpdating = False
Set OrgWS = ActiveSheet
For Each WS In Worksheets
If WS.Visible Then
WS.Select
ActiveWindow.Zoom = 85
Else
WS.Visible = xlSheetVisible
WS.Select
ActiveWindow.Zoom = 85
WS.Visible = xlSheetHidden
End If
Next WS
OrgWS.Select
End Sub

Rgds,

halim

Don Wiss menuliskan:
> I'd like to add a button to my application that sets all the sheet zooms to
> a zoom level as specified by the user. The below works, but is there a
> simpler way?
>
> Sub Zoom()
> Dim WS As Worksheet, OrgWS As Worksheet
> Application.ScreenUpdating = False
> Set OrgWS = ActiveSheet
> For Each WS In Application.Worksheets
> WS.Select
> ActiveWindow.Zoom = 85
> Next WS
> OrgWS.Select
> End Sub
>
> The above would have problems with hidden sheets. So to work properly it
> would have to be modified to detect them, then to rehide them.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

On 10 Aug 2006 19:09:39 -0700, halimnurikhwan@yahoo.com wrote:

>Try this:

Okay. What I was hoping for was some command that did all of the workbook
without looping through the sheets. This is the whole thing fleshed out:

Sub SetSheetZoom()
' is button
Dim WS As Worksheet, OrgWS As Worksheet, Z As String
TryAgain:
Z = InputBox("Enter zoom level", "Sheet Zoom")
If Z = "" Then Exit Sub
If Not IsNumeric(Z) Then
MsgBox "Input must be numeric!", vbCritical, "Not Number"
GoTo TryAgain
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Set OrgWS = ActiveSheet
For Each WS In Worksheets
If WS.Visible Then
WS.Select
ActiveWindow.Zoom = Z
Else
WS.Visible = xlSheetVisible
WS.Select
ActiveWindow.Zoom = Z
WS.Visible = xlSheetHidden
End If
Next WS
OrgWS.Select
Application.EnableEvents = True
End Sub