Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-25-2013, 05:14 PM
BobBridges's Avatar
BobBridges BobBridges is offline Finding the Selection in an inactive worksheet Windows 7 64bit Finding the Selection in an inactive worksheet Office 2010 32bit
Expert
Finding the Selection in an inactive worksheet
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default 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.)
Reply With Quote
  #2  
Old 09-29-2013, 11:01 PM
macropod's Avatar
macropod macropod is offline Finding the Selection in an inactive worksheet Windows 7 32bit Finding the Selection in an inactive worksheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 09-30-2013, 07:05 AM
BobBridges's Avatar
BobBridges BobBridges is offline Finding the Selection in an inactive worksheet Windows 7 64bit Finding the Selection in an inactive worksheet Office 2010 32bit
Expert
Finding the Selection in an inactive worksheet
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 09-30-2013, 01:31 PM
macropod's Avatar
macropod macropod is offline Finding the Selection in an inactive worksheet Windows 7 32bit Finding the Selection in an inactive worksheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 09-30-2013, 02:03 PM
BobBridges's Avatar
BobBridges BobBridges is offline Finding the Selection in an inactive worksheet Windows 7 64bit Finding the Selection in an inactive worksheet Office 2010 32bit
Expert
Finding the Selection in an inactive worksheet
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding the Selection in an inactive worksheet 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
Finding the Selection in an inactive worksheet 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
Finding the Selection in an inactive worksheet "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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:46 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft