Excel 2003 - VBA - Recognizing buttons

[Craig Brandt]

I must be missing something when it comes to buttons.

I have on on my "STRAT" sheet a button called StratAFBut. It's been declared
as a Public Button. When the system reacts to the button being depressed, I
can ensure that it is recognized by adding the following statement, before
I test the button:
Set StratAFBut = ActiveSheet.Buttons(Application.Caller)
I can then test the button without issue.

I have a routine embedded on the STRAT sheet that reacts to a double
click(DC) on a cell. The routine checks to make sure that the DC was in the
expected column, that the entry is the right format and that the button is
in a "ON" state. When I do the test:
If StratAFBut.Caption = "Turn AutoFocus On" Then Exit Sub
I get the following error:
"Object Variable or With Block variable not set"
The above "SET" will not work because the Button was not instrmental in
calling the routine.

How do I get the button recognized?

Thank for your patience with these questions,
Craig

 

[Dave Peterson]

Rightclick on the button.
Look at the namebox (to the left of the formulabar)
Note the name (exactly!)

Then you can use:

set stratafbut = me.buttons("your button name here 1")

 

[Craig Brandt]

Dave:

When I right click on the button, it brings up a popup that among other
things (Exit Edit Text, Grouping, Order, Assign Macro and Format Control),
does not provide me with a name.

This button was created some time ago by a routine that I have long since
discontinued using. Is there another method of obtaining the Name of the
control? At that time I named the control with a ".name = " Line. That has
long since been deleted.

When you suggest the lineof code shown below and let's say the name was
ButtonA would the line read "set stratafbut = me.buttons("ButtonA1")?

set stratafbut = me.buttons("your button name here 1")

Also
Craig

 

[Craig Brandt]

Dave:

It turns out that I actually had two buttons, one right on top of another.
When I deleted one, it seems to work. Since the button was created and named
by a routine that no longer exists, dose it still carry the name it was
created with?

Craig

 

[Dave Peterson]

The button will be named whatever excel defaulted to when you created it. You
can change the name anytime you want.

Manually, you could rightclick on the button and type the new name in that
namebox (remember to hit enter when you're done. Or you could change the name
via code.