Hi - Any assistance gratefully received...
I am trying to toggle between worksheets whilst maintaining the relative scroll position.
The idea being that if I scroll down to row Row 500 in Sheet A... and then click the tab to move to Sheet B... that when Sheet B Activates it sets the scroll position to Row 500. And then if I scroll in Sheet B to Row 1000... when I click on Sheet A and it sets the scroll area to Row 1000.
Couple of things to note - I do not want to use the ActiveCell Row Value since this is NOT what can be visible if I just scroll in sheet without actually selecting... And I am unsure if it complicates matters but I have Freezepane on for Row 1 on both sheets A and B.
My approach has been to use the WorkSheet Activate and DeActivate procedures and using the ActiveWindow.ScrollRow property and picking up the value from a "Support" Sheet where I am storing the Row Value but when I attempt to store the value as I leave the Sheet - it picks up the value from the New Sheet not the Old... ie. the ActiveWindow.ScrollRow is no longer available for the Sheet I am leaving !! (Which is fair enough but doesn't solve my problem!)
Code in both Sheet A and Sheet B is as below...
Any thoughts ?
'
Private Sub Worksheet_Activate()
Range("A" & Sheet13.Range("B14").Value).Select
End Sub
Private Sub Worksheet_Deactivate()
Sheet13.Range("B14").Value = ActiveWindow.ScrollRow
End Sub
'
I am trying to toggle between worksheets whilst maintaining the relative scroll position.
The idea being that if I scroll down to row Row 500 in Sheet A... and then click the tab to move to Sheet B... that when Sheet B Activates it sets the scroll position to Row 500. And then if I scroll in Sheet B to Row 1000... when I click on Sheet A and it sets the scroll area to Row 1000.
Couple of things to note - I do not want to use the ActiveCell Row Value since this is NOT what can be visible if I just scroll in sheet without actually selecting... And I am unsure if it complicates matters but I have Freezepane on for Row 1 on both sheets A and B.
My approach has been to use the WorkSheet Activate and DeActivate procedures and using the ActiveWindow.ScrollRow property and picking up the value from a "Support" Sheet where I am storing the Row Value but when I attempt to store the value as I leave the Sheet - it picks up the value from the New Sheet not the Old... ie. the ActiveWindow.ScrollRow is no longer available for the Sheet I am leaving !! (Which is fair enough but doesn't solve my problem!)
Code in both Sheet A and Sheet B is as below...
Any thoughts ?
'
Private Sub Worksheet_Activate()
Range("A" & Sheet13.Range("B14").Value).Select
End Sub
Private Sub Worksheet_Deactivate()
Sheet13.Range("B14").Value = ActiveWindow.ScrollRow
End Sub
'
