Current (Last Active) Cell in inactive worksheet

[Clif McIrvin]

Excel 2003

Is there a way to discover the "current" cell of an inactive worksheet?

If I tab between worksheets, they always remember the last ActiveCell
.... I'm looking for something along the lines of
Sheets(x).LastActiveCell.

Is this information accessible via VBA?

 

[Dave Peterson]

You can select the other sheet, find the activecell and then change back.

In code...

Option Explicit
Sub testme()

Dim CurLoc As Range
Dim OtherLoc As Range

Set CurLoc = ActiveCell

Application.ScreenUpdating = False
Worksheets("sheet2").Select
Set OtherLoc = ActiveCell
Application.Goto CurLoc
Application.ScreenUpdating = True

MsgBox CurLoc.Address(0, 0) & vbLf & OtherLoc.Address(0, 0)
End Sub

The .screenupdating stuff will prevent the user from even knowing that you
changed sheets.

 

[Clif McIrvin]

Ahh, yes ... ScreenUpdating ... I didn't even think of that.

Sheets("Sheet1").Activate is what I was doing while awaiting a reply.

Thanks!!