I hate getting workbooks in Page Break Preview mode. Okay, hate is a strong word, but it's annoying. I want my workbooks to be in Normal view, so I can edit them. It's especially annoying when you get a workbook with multiple worksheets, and they are all in page break preview mode. You have to manually go through each one and reset the view.
Here's the code I came up with to fix the view for each worksheet. All we do is activate each worksheet, set the view, then return to the originally active worksheet. I would run code like this whenever someone sends me a workbook, to make sure all the sheets are in Normal view.
Sub ResetWindowView()
' store current worksheet, for later reference
Dim thisSht As Excel.Worksheet
Set thisSht = ActiveSheet
Dim sht As Excel.Worksheet
Dim currentWindow As Window
' loop through each worksheet and set view
For Each sht In Excel.Worksheets
If TypeName(sht) = "Worksheet" Then
sht.Activate
Set currentWindow = ActiveWindow
currentWindow.View = xlNormalView
End If
Next sht
' go back to original sheet
thisSht.Activate
End Sub
About JP
2 Comments:
Nice little utility. I have a few questions for you:
(1) Just in case the current sheet is a chart when you start the macro, what data type should thisSht be? I could only get it to work using a Variant type. (If a chart is selected, the current code blows up.)
(2) Since you're cycling through Excel.Worksheets, why are you checking the TypeName()? Aren't they always going to be worksheets?
(3) Is there a reason to use the currentWindow variable? Wouldn't just using the statement ActiveWindow.View = xlNormalView work just as well?
(1) 'As Object' worked for me, but it would be technically be a Chart object. So you could use As Object or As Variant.
(2) You're right that the type check is redundant.
(3) I like using intermediate variables, but you could easily do it your way and save a few lines of code.