Synchronizing Worksheets

When you move from one worksheet in a workbook to another, the sheet you activate will be configured as it was when it was last active. The top-left corner cell, the selected range of cells, and the active cell will be in exactly the same positions as they were the last time the sheet was active, unless you are in Group mode. In Group mode, the selection and active cell are synchronized across the group. However, the top-left corner cell is not synchronized in Group mode, and it is possible that you will not be able to see the selected cells and the active cell when you activate a worksheet.

If you want to synchronize your worksheets completely, even out of Group mode, you can add the following code to the ThisWorkbook module of your workbook:

Dim mshtOldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sht As Object) 'If the deactivated sheet is a worksheet, 'store a reference to it in mshtOldSheet

If TypeName(Sht) = "Worksheet" Then Set mshtOldSheet = Sht End Sub

Private Sub Workbook_SheetActivate(ByVal NewSheet As Object) Dim lCurrentCol As Long Dim lCurrentRow As Long Dim sCurrentCell As String Dim sCurrentSelection As String

On Error GoTo Fin

If mshtOldSheet Is Nothing Then Exit Sub If TypeName(NewSheet) <> "Worksheet" Then Exit Sub Application.ScreenUpdating = False Application.EnableEvents = False mshtOldSheet.Activate 'Get the old worksheet configuration lCurrentCol = ActiveWindow.ScrollColumn lCurrentRow = ActiveWindow.ScrollRow sCurrentSelection = Selection.Address sCurrentCell = ActiveCell.Address

NewSheet.Activate 'Set the new worksheet configuration ActiveWindow.ScrollColumn = lCurrentCol ActiveWindow.ScrollRow = lCurrentRow Range(sCurrentSelection).Select Range(sCurrentCell).Activate Fin:

Application.EnableEvents = True End Sub

The DimmshtOldSheet as Object statement must be at the top of the module in the declarations area, so that mshtOldSheet is 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 Fin statement 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 deactivated 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 Exit Sub statement before the Fin: label, the final statement is executed to make sure event processing is enabled again.

0 0

Post a comment