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