Selection on a non-active sheet?

[Andy Smith]

Let's say on Sheet1 the range A1:B2 is selected, and on Sheet2 that C34 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.

 

[OssieMac]

Hi Andy,

I don't think you can do what you are asking but perhaps if you like to tell
us what you are trying to achieve then I am sure somewone will come up with a
way of doing it. One thing that comes to mind is an Application.InputBox
Method where the user can select the required range when it is required.

 

[Don Guillett]

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