Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 = ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Zoom Property of a WorkBook without using an Array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing zoom level automatically | Excel Worksheet Functions | |||
Zoom Property - 1004 Error | Excel Programming | |||
Changing Workbook Property | Excel Programming | |||
Zoom property | Excel Programming | |||
Zoom property error | Excel Programming |