3

I have a quite huge and complex application based mainly on vba (+3500 lines of code): Its importing and analyzing the wishes for each teacher in my school (also wirtten in Excel). And then, the administrator can edit whatever setting/wish so next year planning of the teacher resources are complete.

It works quite well. And functions almost like a database.

I heavily use Application.ScreenUpdating and Application.EnableEvents to have a nice no-flicker-sensation (visually seeing change of sheets) but sometimes the program is updating several sheets in the same time and it causes flicker-troubles (the program structure is close to perfect, BUT is not perfect)

Is it possible, by any means to get the range of the selection in a sheet without having this sheet being activated? To get the idea: You are on Sheeet1 (Sheet1 activated) and want to get the (range of the) Selection of Sheet2.

I've tried Sheets("Sheet2").Selection.Address or Sheet2.Seledtion.Address, but it doesn't work as Selection is an Application-object.

Any ideas?

Community
  • 1
  • 1
danKV
  • 181
  • 2
  • 12
  • If a worksheet is not the active sheet, it cannot have a selection nor an activecell. Time to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020). –  Mar 31 '18 at 08:07
  • Actually, you've answered your own question with *'it doesn't work as Selection is an Application-object'*; it isn't a property of a worksheet or even workbook. There is one Selection for the entire Excel application instance regardless of how many workbooks or worksheets or windows are open. –  Mar 31 '18 at 08:11
  • @Jeeped. Yes, excactly, but how do I retrieve the rangeaddress, AS IF I actually were on the wanted sheet (each sheet does have a set/default set 'Selection') - the one you see in front of the formula line (i.e. C3)? It must be stored somewhere ... – danKV Mar 31 '18 at 08:28
  • It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats. –  Mar 31 '18 at 08:34
  • @jeeped. I'm interested in your method (even if cheating - I was working as a magician when younger, So it doesn't scare me tooo much) – danKV Mar 31 '18 at 08:40

3 Answers3

1

If a worksheet is not the active sheet, it cannot have a selection nor an activecell. There is one Selection for the entire Excel application instance regardless of how many workbooks or worksheets or windows are open.

This how to cheat: in the ThisWorkbook code sheet put this code.

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Sh.Names("Sel").Delete
    On Error GoTo 0
    Sh.Names.Add Name:="Sel", RefersTo:="='" & Sh.Name & "'!" & Target.Address(1, 1), Visible:=True
End Sub

Anytime any new selection is made on any worksheet within the workbook, the new selection will become a worksheet level defined name. With Sheet1 active, you can refer to the 'selection' on sheet2 with,

worksheets("sheet2").range("Sel")
'on a worksheet as,
=SUM(Sheet2!Sel)

These named ranges are stored with closing the workbook and reopening it.

I cannot in good conscience regard this as a 'best practice'.

Time to read How to avoid using Select in Excel VBA.

  • Thank you for this suggestion. It does need the enableevents TRUE or called by another precedure to work. Are you certain that there is NO way to retrieve these invisibly 'variables' storing the 'selection' for each sheet somehow? Excel needs them all the time, when changing the selection on a sheet or activating another sheet, to make it the new Application.Selection. Even that your method doesn't work fully in my case, you get my vote. – danKV Mar 31 '18 at 10:08
  • Yes, I'm sure there is no way to retrieve inactive 'selection' addresses. –  Mar 31 '18 at 10:11
-1

Drop the word selection if you don't want to Select it

Sheets("Sheet2").Range("A1:B10").yourmethodhere
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet. – danKV Mar 31 '18 at 08:02
-1

Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.

Vineeth Sai
  • 3,389
  • 7
  • 23
  • 34
Carl Shin
  • 1
  • Your answer is not what I asked for. I want to know the address of the selection (i.e. C8) in a sheet, while being - and staying - in another sheet. – danKV Nov 12 '18 at 17:08