How make Excel select the same cell when changing sheets?

Question

I have Excel workbook with 3 sheets. I want to use a macro which will select the same cell when changing sheets.

Example:
I am in sheet1 cell A3 when I switch to sheet2. I want A3 in sheet2 to be selected. Same thing when I switch to sheet3.

Is it possible?
I tried using events sheet_activate, sheet_deactivate, and sheet_change. The last one is surely wrong.

Answer 1

Here is a one-way example. If you start on Sheet1 and select either Sheet2 or Sheet3, you will stay on the same address as you were on Sheet1.

In a standard module, include the single line:

Public addy As String

In the Sheet1 code area, include the following event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    addy = ActiveCell.Address
End Sub

In both the Sheet2 and Sheet3 code areas, include the following event macro:

Private Sub Worksheet_Activate()
    If addy <> "" Then
        Range(addy).Select
    End If
End Sub

Answer 2

You were close. This uses a module-level variable to store the ActiveCell address any time the SheetSelectionChange event fires:

Dim ActiveCellAddress As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
Sh.Range(ActiveCellAddress).Activate
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveCellAddress = ActiveCell.Address
End Sub

Answer 3

I use the following macro to select cell A1 on all sheets within a workbook. I assigned this macro to a button on a toolbar. You can modify it to make it work for when you change sheets.

Sub Select_Cell_A1_on_all_Sheets()
Application.ScreenUpdating = False
On Error Resume Next

Dim J As Integer
Dim NumSheets As Integer
Dim SheetName As String

CurrentSheetName = ActiveSheet.Name

NumSheets = Sheets.Count
For J = 1 To NumSheets
  SheetName = Sheets(J).Name
  Worksheets(SheetName).Activate
  Range("A1").Select
Next J

Worksheets(CurrentSheetName).Activate