Synchronizing Sheets

When you move from one worksheet to another the sheet you activate will be configured as it was when it was last active.
The top-left hand corner cell, the selected range of cells and the active cell will be in exactly the same position as they were the last time that worksheet was active (unless you are in Group mode).
If you are in Group mode, the selected range and the active cell are synchronized across all the worksheets
However the top-left hand corner cell is not synchronized.
(please refer to snippet in scrap book for a way to synchronise properly).


When you switch between worksheets the previous selection and range is preserved
The top left cell, current selection and the active cell are all preserved (ie in the position you left them)
If you are in Group Mode then this is not the case
If you are in Group Mode then the top left cell, current selection and active cell are synchronized across the group.


Public gSheet As Object 

Private Sub Workbook_SheetDeactivate(ByVal Sht As Object)
   If TypeName(Sht) = "Worksheet" Then Set gSheet = Sht
End Sub

Private Sub Workbook_SheetActivate(ByVal NewSheet As Object)
Dim lcol As Long
Dim lrow As Long
Dim sCell As String
Dim sSelection As String

   If gSheet Is Nothing Then Exit Sub
   If TypeName(NewSheet) <> "Worksheet" Then Exit Sub
   If (NewSheet Is Excel.Worksheet) Then Exit Sub

   gSheet.Activate
   lcol = ActiveWindow.ScrollColumn
   lrow = ActiveWindow.ScrollRow
   sSelection = Selection.Address
   sCell = ActiveCell.Address

   NewSheet.Activate
   ActiveWindow.ScrollColumn = lcol
   ActiveWindow.ScrollRow = lrow
   Range(sSelection).Select
   Range(sCell).Activate
End Sub


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext