sCurrentSelection = Selection.Address
sCurrentCell = ActiveCell.Address
NewSheet.Activate ‘Set the new worksheet configuration
ActiveWindow.ScrollColumn = lCurrentCol
ActiveWindow.ScrollRow = lCurrentRow
Application.EnableEvents = True
The DimmshtOldSheetasObject statement must be at the top of the module in the declarations area,
so that mshtOldSheetis a module-level variable that will retain its value while the workbook is open
and can be accessed by the two event procedures. The Workbook_SheetDeactivate event procedure
is used to store a reference to any worksheet that is deactivated. The Deactivate event occurs after
another sheet is activated, so it is too late to store the active window properties. The procedure’s Sht
parameter refers to the deactivated sheet and its value is assigned to mshtOldSheet.
The Workbook_SheetActivate event procedure executes after the Deactivate procedure. The On
Error GoTo Finstatement ensures that, if an error occurs, there are no error messages displayed and
that control jumps to the Fin: label where event processing is enabled, just in case event processing has
been switched off.
The first If tests check that mshtOldSheet has been defined, indicating that a worksheet has been deac-
tivated during the current session. The second If test checks that the active sheet is a worksheet. If
either If test fails, the procedure exits. These tests allow for other types of sheets, such as charts, being
deactivated or activated.
Next, screen updating is turned off to minimize screen flicker. It is not possible to eliminate all flicker,
because the new worksheet has already been activated and the user will get a brief glimpse of its old
configuration before it is changed. Then, event processing is switched off so that no chain reactions
occur. To get the data it needs, the procedure has to reactivate the deactivated worksheet, which would
trigger the two event procedures again.
After reactivating the old worksheet, the ScrollRow (the row at the top of the screen), the
ScrollColumn(the column at the left of the screen), the addresses of the current selection, and the
active cell are stored. The new worksheet is then reactivated and its screen configuration is set to match
the old worksheet. Because there is no ExitSub statement before the Fin: label, the final statement is
executed to make sure event processing is enabled again.
Sum mar y
In this chapter you saw many techniques for handling workbooks and worksheets in VBAcode. You
have seen how to:
Create new workbooks and open existing workbooks.
Handle saving workbook files and overwriting existing files.
Move and copy worksheets and interact with Group mode.
Chapter 3: Workbooks and Worksheets