Hi,

I am trying to write a bit of code that changes the zoom property of a
whole work book. Rather than use an array [as there are 40+ worksheets]
I want to use 'For Each' statement like

Sub ChangeZoom55()

Application.ScreenUpdating = False
Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Zoom = 55
Next

Application.ScreenUpdating = True

Sub Function

There is the added frustration of hidden worksheets, so I am not sure
if I have to run a separate bit of code to un hide the hidden sheets,
if I do have to do this is there a way in which I can hide all the
sheets which were originally hidden but not the sheets that were
visible to start with.

Any help much appreciated. Scott
= )

Hi Scott,

though, with Excel, I am on strange territory,
I think, it is the same as with Word-documents.
Worksheets as well as Word-documents don't have
a zoom property at all. Zoom is a property of a window.

Dim oWnd As Window
Set oWnd = ActiveWindow
oWnd.Zoom = 55

So if there is no window, as with a hidden worksheet,
it might be impossible to set a zoom.

The Excel-experts around will correct me, if I am wrong.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

spences10 wrote:
> Hi,
>
> I am trying to write a bit of code that changes the zoom property of a
> whole work book. Rather than use an array [as there are 40+ worksheets]
> I want to use 'For Each' statement like
>
> Sub ChangeZoom55()
>
> Application.ScreenUpdating = False
> Dim Sh As Worksheet
>
> For Each Sh In Worksheets
> Sh.Zoom = 55
> Next
>
> Application.ScreenUpdating = True
>
> Sub Function
>
> There is the added frustration of hidden worksheets, so I am not sure
> if I have to run a separate bit of code to un hide the hidden sheets,
> if I do have to do this is there a way in which I can hide all the
> sheets which were originally hidden but not the sheets that were
> visible to start with.
>
> Any help much appreciated. Scott
> = )

Hi Scott,

Helmut's right.

I tried...

Public Sub zoomAll()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
Sht.Activate
ActiveWindow.Zoom = 50
Next Sht
End Sub

which worked.

Ken Johnson

Faaaantastic,

thank you both



Ken Johnson wrote:
> spences10 wrote:
> > Hi,
> >
> > I am trying to write a bit of code that changes the zoom property of a
> > whole work book. Rather than use an array [as there are 40+ worksheets]
> > I want to use 'For Each' statement like
> >
> > Sub ChangeZoom55()
> >
> > Application.ScreenUpdating = False
> > Dim Sh As Worksheet
> >
> > For Each Sh In Worksheets
> > Sh.Zoom = 55
> > Next
> >
> > Application.ScreenUpdating = True
> >
> > Sub Function
> >
> > There is the added frustration of hidden worksheets, so I am not sure
> > if I have to run a separate bit of code to un hide the hidden sheets,
> > if I do have to do this is there a way in which I can hide all the
> > sheets which were originally hidden but not the sheets that were
> > visible to start with.
> >
> > Any help much appreciated. Scott
> > = )
>
> Hi Scott,
>
> Helmut's right.
>
> I tried...
>
> Public Sub zoomAll()
> Dim Sht As Worksheet
> For Each Sht In ActiveWorkbook.Worksheets
> Sht.Activate
> ActiveWindow.Zoom = 50
> Next Sht
> End Sub
>
> which worked.
>
> Ken Johnson

spences10 wrote:
> Faaaantastic,
>
> thank you both
>
>
>
> Ken Johnson wrote:
> > spences10 wrote:
> > > Hi,
> > >
> > > I am trying to write a bit of code that changes the zoom property of a
> > > whole work book. Rather than use an array [as there are 40+ worksheets]
> > > I want to use 'For Each' statement like
> > >
> > > Sub ChangeZoom55()
> > >
> > > Application.ScreenUpdating = False
> > > Dim Sh As Worksheet
> > >
> > > For Each Sh In Worksheets
> > > Sh.Zoom = 55
> > > Next
> > >
> > > Application.ScreenUpdating = True
> > >
> > > Sub Function
> > >
> > > There is the added frustration of hidden worksheets, so I am not sure
> > > if I have to run a separate bit of code to un hide the hidden sheets,
> > > if I do have to do this is there a way in which I can hide all the
> > > sheets which were originally hidden but not the sheets that were
> > > visible to start with.
> > >
> > > Any help much appreciated. Scott
> > > = )
> >
> > Hi Scott,
> >
> > Helmut's right.
> >
> > I tried...
> >
> > Public Sub zoomAll()
> > Dim Sht As Worksheet
> > For Each Sht In ActiveWorkbook.Worksheets
> > Sht.Activate
> > ActiveWindow.Zoom = 50
> > Next Sht
> > End Sub
> >
> > which worked.
> >
> > Ken Johnson

You're welcome.
Thanks for the feedback

Ken Johnson

Hi,

there is one thing i have noticed though.

i have just checked the print preview of the document and it has
changed the scaling to like 10% ?

has this anything to do with it?, this is the only this that i have
changed =0

Ken Johnson wrote:
> spences10 wrote:
> > Faaaantastic,
> >
> > thank you both
> >
> >
> >
> > Ken Johnson wrote:
> > > spences10 wrote:
> > > > Hi,
> > > >
> > > > I am trying to write a bit of code that changes the zoom property of a
> > > > whole work book. Rather than use an array [as there are 40+ worksheets]
> > > > I want to use 'For Each' statement like
> > > >
> > > > Sub ChangeZoom55()
> > > >
> > > > Application.ScreenUpdating = False
> > > > Dim Sh As Worksheet
> > > >
> > > > For Each Sh In Worksheets
> > > > Sh.Zoom = 55
> > > > Next
> > > >
> > > > Application.ScreenUpdating = True
> > > >
> > > > Sub Function
> > > >
> > > > There is the added frustration of hidden worksheets, so I am not sure
> > > > if I have to run a separate bit of code to un hide the hidden sheets,
> > > > if I do have to do this is there a way in which I can hide all the
> > > > sheets which were originally hidden but not the sheets that were
> > > > visible to start with.
> > > >
> > > > Any help much appreciated. Scott
> > > > = )
> > >
> > > Hi Scott,
> > >
> > > Helmut's right.
> > >
> > > I tried...
> > >
> > > Public Sub zoomAll()
> > > Dim Sht As Worksheet
> > > For Each Sht In ActiveWorkbook.Worksheets
> > > Sht.Activate
> > > ActiveWindow.Zoom = 50
> > > Next Sht
> > > End Sub
> > >
> > > which worked.
> > >
> > > Ken Johnson
>
> You're welcome.
> Thanks for the feedback
>
> Ken Johnson

spences10 wrote:
> Hi,
>
> there is one thing i have noticed though.
>
> i have just checked the print preview of the document and it has
> changed the scaling to like 10% ?
>
> has this anything to do with it?, this is the only this that i have
> changed =0
>
Hi,

I just took one of my workbooks, checked each sheets appearance in
Print Preview, manually zoomed each sheet to 50%, checked Print Preview
appearances again, and there was no difference.
Then I rezoomed back to 100%, hid one of the sheets, ran the ZoomAll
macro, checked Print Preview appearances, and again there was no
change.

So I don't know what has caused your Print Previews to change.

Ken Johnson