Working with Window Properties in Excel 2010

Office 2010

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how to change the window properties in Microsoft Excel 2010.

Last modified: May 20, 2011

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   June 2011

Provided by:    Frank Rice, Microsoft Corporation

By configuring various properties of the window user interface in Microsoft Excel 2010, you can take advantage of the helpful visual aspects of the worksheet. For example, by displaying horizontal and vertical rulers in the Page Layout view; or displaying and hiding the worksheet gridlines. In this topic, you programmatically change some of the various properties on the worksheet window. To complete this task, you must do the following:

In this task, you add programming code that manipulates the window properties in Excel.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub WindowPropertiesDemo()
        Range("A1", "F10").Formula = "=RANDBETWEEN(1, 100)"
        Range("G1", "G10").Value = 0
       
        Dim wnd As Window
        Set wnd = ActiveWindow
       
        ' Retrieve a reference to the active sheet view:
        Dim view As WorksheetView
        Set view = wnd.ActiveSheetView
       
        Dim doDisplayFormulas As Boolean
        Dim doDisplayGridLines As Boolean
        Dim doDisplayHeadings As Boolean
        Dim doDisplayZeros As Boolean
       
        ' Store away the original values.
        doDisplayFormulas = view.DisplayFormulas
        doDisplayGridLines = view.DisplayGridlines
        doDisplayHeadings = view.DisplayHeadings
        doDisplayZeros = view.DisplayZeros
       
        ' Change the settings for the active sheet view:
        view.DisplayFormulas = Not view.DisplayFormulas
        view.DisplayGridlines = Not view.DisplayGridlines
        view.DisplayHeadings = Not view.DisplayHeadings
        view.DisplayZeros = Not view.DisplayZeros
       
        ' Put things back the way they were originally.
        view.DisplayFormulas = doDisplayFormulas
        view.DisplayGridlines = doDisplayGridLines
        view.DisplayHeadings = doDisplayHeadings
        view.DisplayZeros = doDisplayZeros
       
        ' The DisplayRuler and DisplayWhiteSpace properties
        ' only have an effect in Page Layout view.
           
        ' Store away the current view.
        Dim currentView As XlWindowView
        currentView = wnd.view
       
        ' Make sure the current window is in Page Layout view.
        wnd.view = xlPageLayoutView
       
        ' Store away the current values:
        Dim currentDisplayRuler As Boolean
        Dim currentDisplayWhiteSpace As Boolean
       
        currentDisplayRuler = wnd.DisplayRuler
        currentDisplayWhiteSpace = wnd.DisplayWhitespace
       
        ' Set the properties to the opposite of their current state:
        wnd.DisplayRuler = Not wnd.DisplayRuler
        wnd.DisplayWhitespace = Not wnd.DisplayWhitespace
       
        ' Put things back:
        wnd.DisplayRuler = currentDisplayRuler
        wnd.DisplayWhitespace = currentDisplayWhiteSpace
        wnd.view = currentView
    End Sub
    
    

In this task, you step through the VBA code that modifies the window properties of the worksheet.

To step through the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Now, press F8 to step through the code line-by-line and watch the code behavior.

Show:
© 2014 Microsoft