record current position before leaving worksheet

[rjplante]

I have a workbook with several worksheets on it. I would like to have on each worksheet a short piece of code that will record the current position of the selected cell before I leave the worksheet (select a different sheet) so that when the sheet is activated again, another piece of code will select the last cell the user was in. I currently have VBA code that selects a set of columns and then zooms so that those columns fill the window (Macro 1 below). When the user navigates back to the worksheet, the whole page is selected because of my zoom macro code in Macro 1. To resolve this issue, I would like to have the last known position of the user selected and then scroll to that row minus 5 rows. When I navigate to another worksheet, the value that get entered into cell BA1 from Macro 2 is the value of the current active cell in the worksheet I just activated, not the address of the active cell in the worksheet I just left (deactivated).

How do I record the current active cell address in the active worksheet before I leave the worksheet and select/activate/navigate to another worksheet?

If I can execute the zoom operation in Macro 1 without selecting the range, that would work to solve the problem.

Macro 1

Private Sub Worksheet_Activate()

ActiveWindow.ScrollColumn = 1
'ActiveWindow.ScrollRow = 1

ActiveSheet.Range("A:F").Select
ActiveWindow.Zoom = True

'This is the code I want to implement
Range(Range("BA1").Value).Select
ActiveWindow.ScrollRow = Range("BA1").Row - 5

End Sub

Macro 2

Private Sub Worksheet_Deactivate()

Range("BA1").Value = ActiveCell.Address

End Sub

 

[Fluff]

How about

Private Sub Worksheet_Deactivate()
   Dim sht As Worksheet
   Set sht = ActiveSheet
   Application.EnableEvents = False
   Me.Activate
   Range("BA1").Value = ActiveCell.Address
   sht.Activate
   Application.EnableEvents = True
End Sub

 

[rjplante]

Thanks, That works awesome!!

Quick question so that I can learn, what does the following line of code do?

Me.Activate

I have never encountered that before.

 

[Fluff]

It makes the sheet that contains the code the active sheet.

 

[rjplante]

Ahh thank you.

 

[Fluff]

You're welcome & thanks for the feedback.