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

[Legacy 98055]

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]

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]

Hi Tom

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

 

[Legacy 98055]

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