Resetting Window views in Excel
May 18, 2009 • JP • No Comments • Rate This Article
• Links to this article
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.
' 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
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Site maintenance and upgrade
Next Post: Get-ting Outlook items through VBA
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.