A little nuisance I'd like to do away with. Can you help?

Tom Schreiner · Mar 26th, 2002, 06:18 AM

Hey everyone!
I appreciate the great help in here!

Here's an example of something I have put up with for a long time, but perhaps someone in here has dealt with the same problem.

There are several examples but I'll give you the one I use the most.

I have a Hide and Unhide macros which do the obvious for column headers, sheet tabs, ect, ect...

Why does it seem neccesary that there has to be an active cell before this macros will run with no errors?

If I run this Macro and several others have had the same problem, I recieve a Method of Range type error. It's not usually a huge deal, but sometimes I have not wanted to select any cell for one reason or another.
When I do activate a cell with, activate or select, the macro runs fine...?

This seems to be a problem whenever dealing with Excel objects. No other macros are dependant on this active cell thingamabob.

I appreciate you reading my book and posting a reply.

Tom

[ This Message was edited by: TsTom on 2002-03-26 05:41 ]

paelinck · Mar 26th, 2002, 06:27 AM

I recognize the problem. Some VBA methods expect a cell to be selected in order to work. This is the case for the CurrentRegion method, and probably for a couple of other ones too. The problem occurs when a macro is activated by a command button. The button gets the focus when it is pressed, which deselects the active cell. I usually solve this problem by selecting a cell (e.g. "A1") in the CurrentSheet at the start of the macro.

Marc

Dave Hawley · Mar 26th, 2002, 06:34 AM

Hi Tom

Marc has hit the nail on the head! If the CommandButton is from the Contol toolbox you can set It's TakeFocus******* Property to False as another fix.

Tom Schreiner · Mar 26th, 2002, 06:36 AM

Thanks guys
Am going to test it immediately.
Take focus on click = Not
Got It
Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 06:18 AM	#1
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hey everyone! I appreciate the great help in here! Here's an example of something I have put up with for a long time, but perhaps someone in here has dealt with the same problem. There are several examples but I'll give you the one I use the most. I have a Hide and Unhide macros which do the obvious for column headers, sheet tabs, ect, ect... Why does it seem neccesary that there has to be an active cell before this macros will run with no errors? If I run this Macro and several others have had the same problem, I recieve a Method of Range type error. It's not usually a huge deal, but sometimes I have not wanted to select any cell for one reason or another. When I do activate a cell with, activate or select, the macro runs fine...? This seems to be a problem whenever dealing with Excel objects. No other macros are dependant on this active cell thingamabob. I appreciate you reading my book and posting a reply. Tom [ This Message was edited by: TsTom on 2002-03-26 05:41 ]

Mar 26th, 2002, 06:27 AM	#2
paelinck Board Regular Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	I recognize the problem. Some VBA methods expect a cell to be selected in order to work. This is the case for the CurrentRegion method, and probably for a couple of other ones too. The problem occurs when a macro is activated by a command button. The button gets the focus when it is pressed, which deselects the active cell. I usually solve this problem by selecting a cell (e.g. "A1") in the CurrentSheet at the start of the macro. Marc

Mar 26th, 2002, 06:34 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Tom Marc has hit the nail on the head! If the CommandButton is from the Contol toolbox you can set It's TakeFocus******* Property to False as another fix.

Mar 26th, 2002, 06:36 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Thanks guys Am going to test it immediately. Take focus on click = Not Got It Tom