Put this in the ThisWorkbook module.
Now when you select any cell in any sheet the sheet list will be updated and
the selection address entered. Check sheet 1 to see the results.

Private Sub Workbook_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)
If Target.Count 1 Then Exit Sub
With Sheets("sheet1")
Set fsh = Sheets("Sheet1").Columns("A") _
.Find(What:=Sh.Name, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fsh Is Nothing Then
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(dlr, 1) = Sh.Name
End If
Set fsh = Sheets("Sheet1").Columns("A") _
.Find(What:=Sh.Name, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not fsh Is Nothing Then .Cells(fsh.Row, 2) = Target.Address
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andy Smith" wrote in message
...
Let's say on Sheet1 the range A1:B2 is selected, and on Sheet2 that C3:D4
is
selected. When a user switches between sheets, Excel displays the
selected
(and active) cells correctly, so somehow it keeps track of what cells are
selected on every sheet, even though only one at a time is active.

However the Selection object only applies to the active sheet and window,
so
if Sheet1 were active, how would I find out what cells are selected on
Sheet2
without activating it? I'd like to write "Sheet2.Seletion" or
"Sheet2.RangeSelection", but neither is legal.

--
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC