Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection on a non-active sheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection on a non-active sheet?
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. -- Regards, OssieMac "Andy Smith" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection on a non-active sheet?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Calculate active sheet on sheet selection | Excel Programming | |||
Save As selection in Active Sheet | Excel Programming | |||
Cell selection in non-active sheet | Excel Programming | |||
Determining the Selection on a non-active Sheet? | Excel Programming |