Get range of selection in Excel without activate the sheet
up vote
1
down vote
favorite
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?
excel-vba vba excel
edited Jul 9 at 19:34
Community♦
11
asked Mar 31 at 7:49
danKV
769
add a comment |
up vote
1
down vote
favorite
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?
excel-vba vba excel
edited Jul 9 at 19:34
Community♦
11
asked Mar 31 at 7:49
danKV
769
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.
– Jeeped
Mar 31 at 8: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.
– Jeeped
Mar 31 at 8: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 at 8:28
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8: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 at 8:40
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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?
excel-vba vba excel
edited Jul 9 at 19:34
Community♦
11
asked Mar 31 at 7:49
danKV
769
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?
excel-vba vba excel
excel-vba vba excel
edited Jul 9 at 19:34
Community♦
11
asked Mar 31 at 7:49
danKV
769
edited Jul 9 at 19:34
Community♦
11
asked Mar 31 at 7:49
danKV
769
edited Jul 9 at 19:34
Community♦
11
edited Jul 9 at 19:34
Community♦
11
edited Jul 9 at 19:34
Community♦
11
11
asked Mar 31 at 7:49
danKV
769
asked Mar 31 at 7:49
danKV
769
asked Mar 31 at 7:49
danKV
769
769
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.
– Jeeped
Mar 31 at 8: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.
– Jeeped
Mar 31 at 8: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 at 8:28
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8: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 at 8:40
add a comment |
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.
– Jeeped
Mar 31 at 8: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.
– Jeeped
Mar 31 at 8: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 at 8:28
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8: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 at 8:40
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.
– Jeeped
Mar 31 at 8:07
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.
– Jeeped
Mar 31 at 8: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.
– Jeeped
Mar 31 at 8:11
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.
– Jeeped
Mar 31 at 8: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 at 8:28
@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 at 8:28
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8:34
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8: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 at 8:40
@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 at 8:40
add a comment |
3 Answers
3
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Mar 31 at 9:15
Jeeped
100k135177
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
add a comment |
up vote
0
down vote
Drop the word selection if you don't want to Select it
Sheets("Sheet2").Range("A1:B10").yourmethodhere
answered Mar 31 at 7:58
ashleedawg
12.5k41949
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
add a comment |
up vote
0
down vote
Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
answered Nov 11 at 15:43
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 at 17:08
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Mar 31 at 9:15
Jeeped
100k135177
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
add a comment |
up vote
1
down vote
accepted
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.
answered Mar 31 at 9:15
Jeeped
100k135177
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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.
answered Mar 31 at 9:15
Jeeped
100k135177
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.
answered Mar 31 at 9:15
Jeeped
100k135177
answered Mar 31 at 9:15
Jeeped
100k135177
answered Mar 31 at 9:15
Jeeped
100k135177
answered Mar 31 at 9:15
Jeeped
100k135177
100k135177
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
add a comment |
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
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 at 10:08
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 at 10:08
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
Yes, I'm sure there is no way to retrieve inactive 'selection' addresses.
– Jeeped
Mar 31 at 10:11
add a comment |
up vote
0
down vote
Drop the word selection if you don't want to Select it
Sheets("Sheet2").Range("A1:B10").yourmethodhere
answered Mar 31 at 7:58
ashleedawg
12.5k41949
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
add a comment |
up vote
0
down vote
Drop the word selection if you don't want to Select it
Sheets("Sheet2").Range("A1:B10").yourmethodhere
answered Mar 31 at 7:58
ashleedawg
12.5k41949
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
add a comment |
up vote
0
down vote
up vote
0
down vote
Drop the word selection if you don't want to Select it
Sheets("Sheet2").Range("A1:B10").yourmethodhere
answered Mar 31 at 7:58
ashleedawg
12.5k41949
Drop the word selection if you don't want to Select it
Sheets("Sheet2").Range("A1:B10").yourmethodhere
answered Mar 31 at 7:58
ashleedawg
12.5k41949
answered Mar 31 at 7:58
ashleedawg
12.5k41949
answered Mar 31 at 7:58
ashleedawg
12.5k41949
answered Mar 31 at 7:58
ashleedawg
12.5k41949
12.5k41949
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
add a comment |
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
I don't want to select anything but retrieve the range/address of the selection on the -not-activated- sheet.
– danKV
Mar 31 at 8:02
add a comment |
up vote
0
down vote
Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
answered Nov 11 at 15:43
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 at 17:08
add a comment |
up vote
0
down vote
Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
answered Nov 11 at 15:43
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 at 17:08
add a comment |
up vote
0
down vote
up vote
0
down vote
Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
answered Nov 11 at 15:43
Carl Shin
1
Sheets("Sheet2").Range("A1:B10").Value will be enough if it is just for reading the record.
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
answered Nov 11 at 15:43
Carl Shin
1
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
edited Nov 11 at 15:57
Vineeth Sai
2,28441023
2,28441023
answered Nov 11 at 15:43
Carl Shin
1
answered Nov 11 at 15:43
Carl Shin
1
answered Nov 11 at 15:43
Carl Shin
1
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 at 17:08
add a comment |
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 at 17:08
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 at 17:08
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 at 17:08
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f49585539%2fget-range-of-selection-in-excel-without-activate-the-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– Jeeped
Mar 31 at 8: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.
– Jeeped
Mar 31 at 8: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 at 8:28
It's stored invisibly and cannot be retrieved by the user. There is a way to cheat but it comes with caveats.
– Jeeped
Mar 31 at 8: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 at 8:40