Option Explicit
at the top of each of your modules is your friend--it's never actually required
by VBA, but if you don't use it, you can have trouble.

It tells excel that you're going to declare all your variables. Jim wrote that
he removed this from his test module because your code didn't "DIM" all your
variables (and he didn't want to take the time to do it in his test).

If you have
Option Explicit
at the top of your code, you'll spend less time searching for typos like:

myValue = 5
....later
myVa1ue = 12

(The lower case L (ell) is a 1 (one) in bottom version.)

If you have to declare your variables:

Dim myValue as long

then with this kind of typo, your code won't even get close to running.

==========
There are some things in excel that aren't always necessary and don't need to be
loaded for each workbook.

One of those things provides the ability to access your code
programmably/programmatically.

But you can turn it on for any workbook that needs it.

Inside the VBE with your workbook's project the active project:
tools|references
scroll down the list and put a check mark next to that "microsoft Visual basic
for applications extensibility x.x" entry.



Mark wrote:

Thanks for your post but could you explain to me "VBA Extensibility" and
"Removing Option explicit". - I'm afraid I'm self taught and just use what
works and Option explicit seems to be needed for some reason in most code.

Mark

"Jim Rech" wrote:

Code that runs in 2000 should run in 2003. And your code did run in 2003
for me once I set a reference to VBA Extensibility and got rid of Option
Explicit because you didn't declare all your variables (not a good
practice).

Btw, it's best if you state what/where the error or problem occurs to simply
the task of helping you.

--
Jim Rech
Excel MVP

--

Dave Peterson