Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applicaion.Selection on Excel
Is there any other way of getting the selected range on the individual sheets of workbook after the workbook is opened. The reason is workbook/worksheet must be storing this last selected ranges. So whenever we switch between the sheets the last selected ranges is shown. So from this perspective, is there a way to get it. With Warm Regads, -Rahul Vakil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applicaion.Selection on Excel
Hi Rahul
Put this code on the codesheet for ThisWorkbook, and make sure that CellAddress is pointing to an unused cell. Const CellAddress As String = "A1000" Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each sh In ThisWorkbook.Sheets Range("A1000") = Selection.Address Next End Sub Private Sub Workbook_Open() For Each sh In ThisWorkbook.Sheets sel = Range("A1000").Value Range(sel).Select Next End Sub Regards, Per "RV" skrev i meddelelsen ... Is there any other way of getting the selected range on the individual sheets of workbook after the workbook is opened. The reason is workbook/worksheet must be storing this last selected ranges. So whenever we switch between the sheets the last selected ranges is shown. So from this perspective, is there a way to get it. With Warm Regads, -Rahul Vakil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applicaion.Selection on Excel
A very reasonable question, as you say a non-active sheet's previous
RangeSelection must be stored somewhere (or default A1). However it is not exposed, neither as an object nor as an address.. The RangeSelection and activecell are properties not directly of the sheet but of a Window. A workbook always has at least one window, so FWIW you can get the range selection of a non active wb. For the moment I'll ignore here what occurs with if user opens multiple windows. In passing, it's worth noting that if an embedded worksheet chart is selected, unqualified ActiveCell will fail, would need to do Windows(2).ActiveCell. Somewhere hidden (invisible name-space maybe) the sheet stores certain properties to be applied to window properties when the sheet becomes active. When deactivated, the properties of the newly activated sheet are applied to the Window object, RangeSelection & ActiveCell of the deactivated sheet are no longer accessible. Easy workaround is temporarily reactivate the sheet and get the selection. Set newSh = Activesheet Set ws = Workbooks("abc"),Worksheets("Sheet1") maybe disable screen updating here ws.parent.activate ws.activate set rng = Selection ' verify selection is a range 'or set rng = Windows(1).RangeSelection ' bit more here, not yet 100% sure to return a range newSh.parent.activate newSh.Activate Briefly here's an outline of another workaround - in ThisWorkbook SheetDeactivate - verify the deactivated sheet is a worksheet, If Sh.Type = xlWorksheet then - disable events - trap the new activesheet, Set newSh = ActiveSheet - reactivate the deactivated sheet, Sh.Activate - Sh.Names.Add "LastSelection", Windows(1).RangeSelection (assumes chart not selected) - reactivate, newSh.Activate - re-enable events You can then reference the "lastSelection" from "'sheet-Name'!LastSelection", if the name doesn't exist use default A1 Regards, Peter T "RV" wrote in message ... Is there any other way of getting the selected range on the individual sheets of workbook after the workbook is opened. The reason is workbook/worksheet must be storing this last selected ranges. So whenever we switch between the sheets the last selected ranges is shown. So from this perspective, is there a way to get it. With Warm Regads, -Rahul Vakil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Combo Box selection only shows bound column info after selection made. | Excel Programming | |||
How to create a selection list then display the selection in a cell | Excel Programming | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming |