Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
Hi All,
Below is a code snippet from a routine that loops through each sheet in a workbook. The routine copies / pastes ranges into powerpoint so l need to ensure the sheet is in normal view to avoid pages numbers etc being passed to PP Sht1.Activate If ActiveWindow.View = xlPageBreakPreview Then ShtView = "Yes" ActiveWindow.View = xlNormalView End If How can l achieve this without using Sht1.Activate? I want to avoid the 'flashing' caused by the Sht1.Activate. If l use Application.Screenupdating = False the data is not passed to PP Regards Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
Just a guess...
If Sht1.View = xlPageBreakPreview Then ShtView = "Yes" ActiveWindow.View = xlNormalView End If -- Ian -- "michael.beckinsale" wrote in message ... Hi All, Below is a code snippet from a routine that loops through each sheet in a workbook. The routine copies / pastes ranges into powerpoint so l need to ensure the sheet is in normal view to avoid pages numbers etc being passed to PP Sht1.Activate If ActiveWindow.View = xlPageBreakPreview Then ShtView = "Yes" ActiveWindow.View = xlNormalView End If How can l achieve this without using Sht1.Activate? I want to avoid the 'flashing' caused by the Sht1.Activate. If l use Application.Screenupdating = False the data is not passed to PP Regards Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
Hi IanC,
Sorry you guessed wrong! Already tried that and l suspect it fails because 'View' is not a property of a Sheet, Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
Doing things with the Windows object is one of the few occasions you do need
to use select or activate. Though in this case you could do all in one go, select all sheets, change the view to xlNormalView (if the activesheet was already xlNormalView you'd need to change all to xlPageBreakPreview first) But why not loop through your sheets first with screenupdating disabled and set each view as required (only if necessary). Perhaps store any changed settings in an array to be reset when done. Enable screenupdating and do your stuff. IOW two loops, or perhaps three if you want to reset. Regards, Peter T "michael.beckinsale" wrote in message ... Hi All, Below is a code snippet from a routine that loops through each sheet in a workbook. The routine copies / pastes ranges into powerpoint so l need to ensure the sheet is in normal view to avoid pages numbers etc being passed to PP Sht1.Activate If ActiveWindow.View = xlPageBreakPreview Then ShtView = "Yes" ActiveWindow.View = xlNormalView End If How can l achieve this without using Sht1.Activate? I want to avoid the 'flashing' caused by the Sht1.Activate. If l use Application.Screenupdating = False the data is not passed to PP Regards Michael |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
Hi Peter,
I was afraid that was the response l was going to get. I have taken your suggestion on board and created loops to set the worksheet views, store in an array, and then restore views accordingly. It seems a disproportionate amount of work to simply set the view but l suppose thats Microsoft / VBA ! Thanks very much for your kind help over the past couple of days. Regards Michael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change view without activating sheet?
In the big scheme of things it's not that much work -
Sub test() Dim i As Long Dim shtOrig As Object ReDim bArr(1 To Worksheets.Count) As Boolean Set shtOrig = ActiveSheet Application.ScreenUpdating = False For i = 1 To Worksheets.Count Worksheets(i).Select If ActiveWindow.View = xlPageBreakPreview Then ActiveWindow.View = xlNormalView bArr(i) = True End If Next shtOrig.Select Application.ScreenUpdating = True ' Do stuff Stop ' have a look Application.ScreenUpdating = False For i = 1 To Worksheets.Count If bArr(i) = True Then Worksheets(i).Select ActiveWindow.View = xlPageBreakPreview End If Next shtOrig.Select Application.ScreenUpdating = True End Sub Regards, Peter T "michael.beckinsale" wrote in message ... Hi Peter, I was afraid that was the response l was going to get. I have taken your suggestion on board and created loops to set the worksheet views, store in an array, and then restore views accordingly. It seems a disproportionate amount of work to simply set the view but l suppose thats Microsoft / VBA ! Thanks very much for your kind help over the past couple of days. Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
view different parts of sheet - split view | Excel Discussion (Misc queries) | |||
TRUE/FALSE BOX not activating a WS change | Excel Discussion (Misc queries) | |||
[help]how to change embedded excel sheet 's view range? | Excel Programming | |||
Change from userform view to excel workbook view | Excel Programming | |||
View Custom View with Sheet Protection | New Users to Excel |