Simultaneous scrolling for 3 worksheets.
Hi Tom Thumb,
I tried using worksheet activate and deactivate events first off and
failed miserably.
I could vaguely remember seeing something about worksheet
synchronisation in John Green's Excel 2000 VBA Programmer's
Reference.
Here's the code which must be pasted into the ThisWorkbook code
module.
It might suit your needs as is, otherwise you could make your own
changes.
I tried it myself and it works fine...
Dim OldSheet As Object
Private Sub Workbook_SheetDeactivate(ByVal Sht As Object)
'if the deactivated sheet is a worksheet, store a reference to it in
'OldSheet
If TypeName(Sht) = "Worksheet" Then Set OldSheet = Sht
End Sub
Private Sub Workbook_SheetActivate(ByVal NewSheet As Object)
Dim lCurrentCol As Long
Dim lCurrentRow As Long
Dim stCurrentCell As String
Dim stCurrentSelection As String
On Error GoTo Fin
If OldSheet Is Nothing Then Exit Sub
If TypeName(NewSheet) < "Worksheet" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
OldSheet.Activate 'get the old worksheet configuration
lCurrentCol = ActiveWindow.ScrollColumn
lCurrentRow = ActiveWindow.ScrollRow
stCurrentSelection = Selection.Address
stCurrentCell = ActiveCell.Address
NewSheet.Activate 'Set the new worksheet configuration
ActiveWindow.ScrollColumn = lCurrentCol
ActiveWindow.ScrollRow = lCurrentRow
Range(stCurrentSelection).Select
Range(stCurrentCell).Activate
Fin:
Application.EnableEvents = True
End Sub
Ken Johnson
|