A sample procedure in a workbook module

By default, the workbook module in a workbook is named ThisWorkbook. Each Excel file has only one workbook module.

 

A simple event handler: Workbook_Open

Suppose that you want to limit the scroll area in a particular worksheet of a workbook when the workbook is opened. Execute the following steps:

  1. Start a blank workbook, or open an existing workbook.
  2. Press Alt+F11 to activate VBE.
  3. In the Project Explorer window, double-click ThisWorkbook to activate the code window of the workbook module.
  4. In the code window, select Workbook from the Object drop-down list, which is located on the top-left corner of the code window. See Figure 1-1.
  5. By default, the Open event is automatically selected from the Procedure drop-down list, which is located on the top-right corner of the code window.
    • Try to select other events from the Procedure drop-down list. Once a new item is selected, an empty event handler of the selected event is inserted into the code window.
  6. To set the scroll area for a particular worksheet (say, the first worksheet) of the workbook to the range A1:N20, enter the following VBA code into the code window:
Private Sub Workbook_Open()
   Worksheets(1).ScrollArea = "A1:N20"
End Sub
  1. Save the workbook as a macro-enabled workbook (*.xlsm).
  2. Close and reopen the workbook.
  3. If you are prompted with a security warning about active or macro content, you need to enable the content. Otherwise, the Workbook_Open event-handler procedure and any other procedures to be written by you will not work.

To clear the scrolling limit, execute the following statement in the Immediate window:

Worksheets(1).ScrollArea = ""

An alternative to set and clear the scrolling limit for a particular worksheet without using VBA code is by changing the ScrollArea property in the Properties window of the worksheet. In VBE, press F4 (or in Excel, choose Developer | Controls | Properties) to display the Properties window.