Selection on a non-active sheet?

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.
 
O

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top