Selection on a non-active sheet?

  • Thread starter Thread starter Andy Smith
  • Start date Start date
A

Andy Smith

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.
 
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.
 
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
 
Back
Top