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

Ken,

I cannot thank you enough for the great gift you gave me! I enjoy using it
every day. I wonder who you are, but have narrowed my questions down to what
I hope will be a comfortable 2. What is your age and City? I am 65
Pittsburgh.
--
TomThumb


"Ken Johnson" wrote:

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

On May 5, 1:16 am, TomThumb
wrote:
Ken,

I cannot thank you enough for the great gift you gave me! I enjoy using it
every day. I wonder who you are, but have narrowed my questions down to what
I hope will be a comfortable 2. What is your age and City? I am 65
Pittsburgh.
--
TomThumb

Hi TomThumb,

I'm 59 and live in Sydney Australia.

I teach high school science and looking forward to retirement January
2010.
This year I'm on long service leave (full pay) to help my youngest
prepare for the Higher School Certificate exams in Oct-Nov. He hates
school and is difficult to motivate in anything other than computing.
His two older siblings were much easier by comparison but when any of
our computers stop working he is invariably the one to fix it.

It's nice to hear the code is so useful. Full credit though goes to
John Green, the main author of the book I mentioned earlier, I just
copied it straight from page 91, checked that it works then passed it
on.
According to the book, John is an MVP and also resides in Sydney.

What about yourself?
Are you retired?
When I reach your age I will have been retired for 5 years and still
trying to master Excel. I doubt that I will achieve mastery but hoping
that the mental stimulation will stop me going senile too soon.

Ken

Ken, I forgot that you had previously mentioned your source, John Green. I
retired from teaching high school math & computer science in 1996 at age 54.
I've also been using VBA as mental stimulation to, in part, keep from going
senile. I visited Sydney last Christmas and dined in the revolving
restaurant in the tower. Great city & harbor. Thanks again for taking my
problem seriously and helping me greatly. Good Luck!
--
TomThumb


"Ken Johnson" wrote:

On May 5, 1:16 am, TomThumb
wrote:
Ken,

I cannot thank you enough for the great gift you gave me! I enjoy using it
every day. I wonder who you are, but have narrowed my questions down to what
I hope will be a comfortable 2. What is your age and City? I am 65
Pittsburgh.
--
TomThumb

Hi TomThumb,

I'm 59 and live in Sydney Australia.

I teach high school science and looking forward to retirement January
2010.
This year I'm on long service leave (full pay) to help my youngest
prepare for the Higher School Certificate exams in Oct-Nov. He hates
school and is difficult to motivate in anything other than computing.
His two older siblings were much easier by comparison but when any of
our computers stop working he is invariably the one to fix it.

It's nice to hear the code is so useful. Full credit though goes to
John Green, the main author of the book I mentioned earlier, I just
copied it straight from page 91, checked that it works then passed it
on.
According to the book, John is an MVP and also resides in Sydney.

What about yourself?
Are you retired?
When I reach your age I will have been retired for 5 years and still
trying to master Excel. I doubt that I will achieve mastery but hoping
that the mental stimulation will stop me going senile too soon.

Ken

Hi there!

I happened to find your conversation and I am right in the middle of the same problem that TomThumb had before he got Ken's solution. However, I am an Excel beginner and I have no clue on how to get this code working.

I have pasted it into my "ThisWorkBook", saved everything, reopened the file and checked so that the code is still there. I allow macros and the document opens up but then when I scroll I still do not get the sheets to scroll simultainously.

Since I am a beginner and I know of no VBA perhaps I am missing something. I would be really glad if one of you guys could help me out as this would be really useful for me at work.

Thanks in advance!

/Martin

This is directly from "ThisWorkbook" in my app:

Option Explicit
Dim OldSheet As Object

Private Sub Workbook_Open()
If ActiveCell.Worksheet.Name = Sheet1.Name Then Sheet1.Calculate
If ActiveCell.Worksheet.Name = Sheet7.Name Then
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End If
If (ActiveCell.Worksheet.Name = Sheet1.Name) Or (ActiveCell.Worksheet.Name =
Sheet3.Name) _
Or (ActiveCell.Worksheet.Name = Sheet4.Name) Then
ActiveWindow.ScrollColumn = 3
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sht As Object)
If (TypeName(Sht) = "Worksheet") And ((Sht.Name = Sheet1.Name) _
Or (Sht.Name = Sheet3.Name) Or (Sht.Name = Sheet4.Name)) Then Set
OldSheet = Sht
End Sub

Private Sub Workbook_SheetActivate(ByVal NewSheet As Object)
Dim OldScrollCol As Long
Dim OldScrollRow As Long
Dim OldSelection As String
Dim Available As Currency
On Error GoTo Finish
If Not (OldSheet Is Nothing) And (TypeName(NewSheet) = "Worksheet") Then
If (NewSheet.Name = Sheet1.Name) Or (NewSheet.Name = Sheet3.Name) _
Or (NewSheet.Name = Sheet4.Name) Then
Application.ScreenUpdating = False
Application.EnableEvents = False
OldSheet.Activate 'get the old worksheet configuration
OldScrollCol = ActiveWindow.ScrollColumn
OldScrollRow = ActiveWindow.ScrollRow
OldSelection = ActiveWindow.RangeSelection.Address
NewSheet.Activate 'Set the new worksheet configuration
ActiveWindow.ScrollColumn = OldScrollCol
ActiveWindow.ScrollRow = OldScrollRow
NewSheet.Range(OldSelection).Select
End If
End If
Finish:
If (NewSheet.Name = Sheet1.Name) Then Sheet1.Calculate
If (NewSheet.Name = Sheet7.Name) Then
Available = Sheet1.Cells(Sheet1.Cells(1, 27) - 1, Month(Date) + 2)
Sheet7.Cells(3, 2) = Available
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

This code extends the previous code to conserve selected areas as well.
--
TomThumb


"Martin Lindberg" wrote:

Hi there!

I happened to find your conversation and I am right in the middle of the same problem that TomThumb had before he got Ken's solution. However, I am an Excel beginner and I have no clue on how to get this code working.

I have pasted it into my "ThisWorkBook", saved everything, reopened the file and checked so that the code is still there. I allow macros and the document opens up but then when I scroll I still do not get the sheets to scroll simultainously.

Since I am a beginner and I know of no VBA perhaps I am missing something. I would be really glad if one of you guys could help me out as this would be really useful for me at work.

Thanks in advance!

/Martin

On Jun 5, 12:27 am, Martin Lindberg wrote:
Hi there!

I happened to find your conversation and I am right in the middle of the same problem that TomThumb had before he got Ken's solution. However, I am an Excel beginner and I have no clue on how to get this code working.

I have pasted it into my "ThisWorkBook", saved everything, reopened the file and checked so that the code is still there. I allow macros and the document opens up but then when I scroll I still do not get the sheets to scroll simultainously.

Since I am a beginner and I know of no VBA perhaps I am missing something. I would be really glad if one of you guys could help me out as this would be really useful for me at work.

Thanks in advance!

/Martin

Hi Martin and TomThumb,

It doesn't sound like you're doing anything wrong Martin so I can't
see why it isn't working.
Maybe your definition of worksheet synchronization is different to
ours.
With this code, when the user changes sheets the new active sheet will
be positioned the same as the previous active sheet. For example, if
the top left cell on the previous active sheet was AA55 then the new
active sheet will have AA55 in the top left corner. Also, the range of
selected cells on the new active sheet will be the same as the range
of cells that were selected on the previous active sheet.
I tried TomThumb's altered code and discovered that the changes made
appear to suit only workbooks set up a particular way eg at least 7
worksheets are needed to avoid an error and a cell on sheet1 is
referred to that results in an error if the value in that cell is less
than 3.

TomThumb, I'm not sure what you mean here...

This code extends the previous code to conserve selected areas as well.

I thought that the original code already does this. Or, is my
understanding of "conserve selected areas" wrong?

If you are still having problems getting it to work Martin you could
email me a workbook with your attempt for me to have a look at...

"gmail.com" account with name "kencjohnson"

Ken Johnson