Applicaion.Selection on Excel

[RV]

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

 

[Per Jessen]

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

 

[Peter T]

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