Specifying a Valid Range

The second way to establish boundaries is to specify a valid range in the worksheet's Properties window. Right-click the sheet's tab at the bottom left of the window and select View Code from the context menu. If you are using a version of Excel before 2007, you may need to select View → Project Explorer (Ctrl-R) on Windows to visit the Project Explorer (it is always visible on the Mac). If the Properties window isn't visible, press F4 to make it appear. Select the appropriate worksheet and visit the ScrollArea property in the Properties window.

Now, from within the Project Explorer, select the worksheet you want the scroll area limited to, and then, from the Properties window go down to the ScrollArea property. In the associated value field to the right, enter the preferred boundaries of your worksheet- $A$1:$G$50, for instance.

You will be unable to scroll outside the area you have specified. Unfortunately, Excel will not save this setting after you close the window. This means you need a very simple macro to automatically set the scroll area to the desired range by placing some code in the worksheet_Activate event. Right-click the Sheet Name tab on which the scroll area should be limited, select View Code, and then enter the following:

    Private Sub Worksheet_Activate ( )
    Me.ScrollArea = "A1:G50"
    End Sub

As usual, press Alt-Q to return to Excel proper and save your workbook.

Although you will not see a visible clue, such as the moat of the first method, you won't be able to scroll or select anything outside the specified area.

Any macro that tries to select a range outside this scroll area (including selections of entire rows and columns) will no longer be able to do so. This is true particularly for recorded macros, as they often use selections.

If your macros do select a range outside the scrollable area, you can easily modify any existing macros so that they are not limited to a specific scroll area while operating. Simply select View → Macros under Macro options, or Developer → Macros under Code options, or Alt/Option-F8 (pre-2007, Tools → Macro → Macros...), then locate your macro name, select it, and click Edit.

Place the following line of code as the very first line of code:

    ActiveSheet.ScrollArea = ""

As the very last line of code in your macro, place the following:

    ActiveSheet.ScrollArea = "$A$1:$G$50"

So, your code should look something like this:

    Sub MyMacro( )
    '
    ' MyMacro Macro
    ' Macro recorded 19/9/2007 by OzGrid.com
    '
    '
    ActiveSheet.ScrollArea = ""
     Range("Z100").Select
     Selection.Font.Bold = True
    ActiveSheet.ScrollArea = "$A$1:$G$50"
    Sheets("Daily Budget").Select
    ActiveSheet.ScrollArea = ""
     Range ("T500").Select
     Selection.Font.Bold = False
    ActiveSheet.ScrollArea = "$A$1:$H$25"
 
    End Sub

Our recorded macro selects cell Z100 and formats it to boldface. It then selects the worksheet named Daily Budget, selects cell T500 on that sheet, and un-bolds it. We added ActiveSheet.ScrollArea = "" so that any cell on the worksheet can be selected and then the scroll area can be set back to our desired range. When we select another worksheet (Daily Budget), we again allowthe code to select any cell on this worksheet and set the scroll area for this worksheet back to the desired range.

[Previous] [Contents] [Next]

Specifying a Valid Range

In this tutorial: