Change view without activating sheet?

[michael.beckinsale]

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

 

[IanC]

Just a guess...

If Sht1.View = xlPageBreakPreview Then
ShtView = "Yes"
ActiveWindow.View = xlNormalView
End If

 

[michael.beckinsale]

Hi IanC,

Sorry you guessed wrong!

Already tried that and l suspect it fails because 'View' is not a
property of a Sheet,

Michael

 

[Peter T]

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]

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

 

[Peter T]

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