It would be a real convenience for me to be able to scroll to row 159 of
spreadsheet A and then when I click on spreadsheet B or C, the scroll row is
preset to 159 on those worksheets. Any help would be greatly appreciated.
--
TomThumb

On Apr 23, 7:52 am, TomThumb
wrote:
It would be a real convenience for me to be able to scroll to row 159 of
spreadsheet A and then when I click on spreadsheet B or C, the scroll row is
preset to 159 on those worksheets. Any help would be greatly appreciated.
--
TomThumb

One way...

Assign these two macros to suitably captioned buttons (eg "Goto B" and
"Goto C") on a new toolbar named "Sync Scroll Row"...

Public Sub SyncToB()
Dim AScrollRow As Long
AScrollRow = ActiveWindow.ScrollRow
Worksheets("B").Activate
ActiveWindow.ScrollRow = AScrollRow
End Sub
Public Sub SyncToC()
Dim AScrollRow As Long
AScrollRow = ActiveWindow.ScrollRow
Worksheets("C").Activate
ActiveWindow.ScrollRow = AScrollRow
End Sub

Add these two Event Procedures to worksheet A...

Private Sub Worksheet_Activate()
Application.CommandBars("Sync Scroll Row").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Sync Scroll Row").Visible = False
End Sub

The Sync Scroll Row toolbar will then only be visible in Worksheet A.
Use the toolbar to get to worksheets B and C with the same scroll row
as worksheet A.

First add the SyncToB and SyncToC macros to a general code module.
Then use go View|Toolbars|Customise... and use the Customise dialog to
make the Sync Scroll Row toolbar. Then add the Event Procedures to the
Worksheet A code module.

Ken Johnson

Ken, Thank you for giving me your code and directions for implementing it.
I really appreciate that you spent your time on my problem. I did, however,
have a different scenario in mind. I don't want a toolbar the user has to
remember to use, but rather that when the user has spreadsheet A open and
then clicks on the tab for spreadsheets B or C, they are viewing the same
scrollrow as spreadsheet A. It would even be nicer if changes to the
scrollrow of spreadsheets B or C would be reflected in A. I want the user to
do nothing special, except click on spreadsheets A, B, or C, to achieve
synchrony.
--
TomThumb


"Ken Johnson" wrote:

On Apr 23, 7:52 am, TomThumb
wrote:
It would be a real convenience for me to be able to scroll to row 159 of
spreadsheet A and then when I click on spreadsheet B or C, the scroll row is
preset to 159 on those worksheets. Any help would be greatly appreciated.
--
TomThumb

One way...

Assign these two macros to suitably captioned buttons (eg "Goto B" and
"Goto C") on a new toolbar named "Sync Scroll Row"...

Public Sub SyncToB()
Dim AScrollRow As Long
AScrollRow = ActiveWindow.ScrollRow
Worksheets("B").Activate
ActiveWindow.ScrollRow = AScrollRow
End Sub
Public Sub SyncToC()
Dim AScrollRow As Long
AScrollRow = ActiveWindow.ScrollRow
Worksheets("C").Activate
ActiveWindow.ScrollRow = AScrollRow
End Sub

Add these two Event Procedures to worksheet A...

Private Sub Worksheet_Activate()
Application.CommandBars("Sync Scroll Row").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Sync Scroll Row").Visible = False
End Sub

The Sync Scroll Row toolbar will then only be visible in Worksheet A.
Use the toolbar to get to worksheets B and C with the same scroll row
as worksheet A.

First add the SyncToB and SyncToC macros to a general code module.
Then use go View|Toolbars|Customise... and use the Customise dialog to
make the Sync Scroll Row toolbar. Then add the Event Procedures to the
Worksheet A code module.

Ken Johnson

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

Ken, you are a genius!, your code worked exactly as I wanted, as is!
Thank you so much for exploring worksheet activate and deactivate -- I
failed miserably there too. Thanks again, Ken, WOW! You deserve to be an MVP
--
TomThumb


"Ken Johnson" wrote:

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

On Apr 24, 3:42 am, TomThumb
wrote:
Ken, you are a genius!, your code worked exactly as I wanted, as is!
Thank you so much for exploring worksheet activate and deactivate -- I
failed miserably there too. Thanks again, Ken, WOW! You deserve to be an MVP
--
TomThumb

"Ken Johnson" wrote:
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

You're welcome Tom Thumb.
Thanks for the feedback.

Ken Johnson