#1
|
||||
|
||||
Finding the Selection in an inactive worksheet
I've been writing in VBA for Excel for years, and I just now got around to noticing that there's no Selection property for a worksheet; I guess it's just for the ActiveWindow. Yet now that I see that, what I want is to have my program detect which cell(s) are selected in a worksheet that isn't necessarily active. Surely it can be done; Excel knows, after all, for when I return to the worksheet the same cells are selected there that were before. So how can my program determine that?
(I realize I can Activate the worksheet; and it isn't even all that important not to do it, as it might be if, for example, I had to do it many times in a loop with many worksheets. Still, there must be a way.) |
#2
|
||||
|
||||
AFAIK there's no vba method for this (other than activating the sheet). In any event, with good coding one would rarely need to either select a cell or know which cell was selected on any sheet except, perhaps, the active one.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
The reason I asked is this: In order to give a program its starting directions, of course sometimes it's necessary to use InputBox. But if the "direction" is simply to tell the program what range, worksheet, workbook etc to work with, I prefer to let the user indicate that parameter by by positioning the cursor before starting the program. So rather than have the user start the program and then enter a date in InputBox (as an OP did here recently) so that the program can search for that date and use the resulting column for input, the user will usually find it easier to put the cursor somewhere in that column before starting the program, and let the program use the active cell to indicate the column.
But I don't like my programs to assume any more about the user's behavior than they have to. What if he positions the cursor, then absentmindedly swaps to another window before starting the macro? In that case Excel can tell, because ActiveCell tells us what cell is active within Excel. But what if I know which sheet is the critical one and the user doesn't happen to be looking at that sheet when the program starts? How do I tell then what cell is active in the critical sheet? ... or rather, which cell would be the active one if that sheet were active? If there's no way to do it and I must be sure, then I can just activate the necessary sheet first. In fact, it isn't that hard to record which sheet is active, switch to the important one and then switch back before exiting the program, if necessary. But I thought I'd ask, because after all Excel knows, so I thought it might be available to VBA as well. |
#4
|
||||
|
||||
Hi Bob,
Relying on users to select a particular cell on a sheet that is to be active when the macro is run is fraught with danger. It's far better to have the code work out for itself wherever possible what ranges it ought to work with; either that or have copious code for selection/activation validation and error-checking. To be sure, Excel 'knows' what cell(s) are selected on every sheet, but that's not to say those data are exposed to the vba object model when a sheet is inactive. In all Office applications, there are features/functions available via the GUI that are not exposed to vba.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
Oh, don't misunderstand me, macropod:
1) If the code can work out for itself what ranges it should work with, that's always better. I'm saying only that when the use must tell the program something, and you can choose between pointing and typing, pointing is usually both easier for the user and more reliable. 2) Whether the user points or types, the program absolutely should do a lot of checking and be prepared to say "No, not there", and "you must be in one of these two worksheets to run this macro", and "this workbook is not a valid target", and so forth. That's even when I am the user; I may know what to do most of the time, but some day I'll be absent-minded, and if my program says "you specified range R but constant C specifies option Z. Switching to mode M", I'll feel proud that I thought ahead. 3) I am usually the user . The above still applies, though. About the only way I get careless on my own behalf is that in response to certain kinds of obvious errors, rather than abending with a clear error message I may just Stop with a comment. For end users this'd be a bad idea, but for me it allows the opportunity of fixing the program and moving on without having to start over. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Selection of all Text for a specific page in word is spanning selection across pages | ramsgarla | Word VBA | 9 | 12-05-2012 03:23 AM |
How to summarise different worksheet to a summary worksheet | samkiewhock | Excel | 1 | 09-06-2012 03:34 AM |
finding more the one | gsrikanth | Excel | 0 | 05-14-2012 03:25 AM |
"Auto-populating" data-worksheet to worksheet. | meggenm | Excel | 4 | 02-04-2012 02:04 AM |
Options Buttons become inactive | clint | Word | 0 | 02-25-2010 04:31 PM |