Retrieve the current selection of an inactive worksheet ?

[Jaafar Tribak]

Is it possible to determine the range currently selected in an inactive worksheet without activating the worksheet ?

Application.Selection only works for the activesheet.

 

[sijpie]

Now using Excel 2007 Win XP

You poor thing.... Excel 2007!


Anyway about your question. According to the object browser Selection is only a member of Application or Window, in which case you are out of luck, and you will have to activate the sheet first.

I must admit it is rather strange that you can't find out where Excel stores the selected cells for each sheet. I tried several things, but the compiler complained.

 

[RSXchin]

you could define a range maybe...

 

[Jaafar Tribak]

Thanks for the responses.

I must admit it is rather strange that you can't find out where Excel stores the selected cells for each sheet. I tried several things, but the compiler complained.

XL 2007 workbooks are compressed files. If you uncompress the workbook and open the xl\worksheets Folder, you will find the worksheets contained in the workbook. Try opening each sheet xml file and you will find where the selected cells are stored.
Obviously, this assumes the workbook is first saved.

 

[Jaafar Tribak]

One could easily cheat their way by cancelling the screenupdating and events as follows but i am still looking for a more elegant solution.

Sub Test()

    Dim oActiveSheet As Worksheet
    Dim oSh As Worksheet
    
    Set oActiveSheet = ActiveSheet
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        For Each oSh In ThisWorkbook.Worksheets
            oSh.Activate
            MsgBox "Selected Range in " & oSh.Name & " : " & Selection.Address
        Next
        oActiveSheet.Activate
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[sijpie]

Well screenupdating = false is one of the first things I implement whenever more than one sheet or lots of actions are concerned, as it also mightly speeds up the running of the macros.

As there is no public property of the worksheets or sheets object that refers to the selected cell(s) on that sheet, there isn't likely to be a more elegant method to choose than to activate the sheet(s) and get the range(s).

 

[Jaafar Tribak]

As there is no public property of the worksheets or sheets object that refers to the selected cell(s) on that sheet, there isn't likely to be a more elegant method to choose than to activate the sheet(s) and get the range(s).

Yep- There exists no Property that exposes the address of selected cells in inactive worksheets which is really odd and disappointing on the part of the Excel software designers.