How to create control button on worksheet: Forms v. ActiveX feature?

[curiousgeorge408]

I wanted to create a control button (correct terminology?) on a
worksheet to execute a macro. I stubbled across two different types
of control buttons: one created by the Forms toolbar; the other
created by the Control Toolbox toolbar, which I understand is an
ActiveX control.

Since my macro was already written, the Forms control button seems
more straight-forward to associate a pre-existing macro with a control
button.

But I actually stumbled across the ActiveX control button first. That
required that I "rewrite" (cut-and-paste) my macro into an Excel
Object Sheet1 window instead of the Module1 window.

(Okay, I could have called the pre-existing macro from the "click"
macro. More about that below.)

This got me to wonder.... What are the pros and cons of each
approach? Why are there two appoaches?

I suspect the answer to the latter question is: I am not using one or
the other feature as it is intended to be used. Please elaborate.

Some things that I observed. Your comments would be appreciated....

First, as I said, the Forms control button set-up seemed more straight-
forward.

However, when I click on the ActiveX control button, I get visual
feedback. I don't get any visual feedback when I click on the Forms
control button.

Is there something that I need to do in order to get visual feedback
from the Forms control button? Or is that just the way the Forms
control button works?

(WAG: Perhaps because it normally brings up a user form, which would
serve as sufficient visual feedback.)

Second, I encountered a name conflict error when I tried to call the
pre-existing macro from the ActiveX "click" macro created in the Excel
Object window. That was actually due to my mistake, and I now know
how to avoid it. But I'm curious: what was the name conflict?

Here is what happened. After I created the Active control button
(with default name CommandButton1), I right-clicked on the button,
clicked Properties, and changed Name (as well as Caption) to Foobar,
the name of the Module1 macro. When I clicked on View Code, that
created the Excel Object Sheet1 macro to Foobar_Click(). In the VBE,
I entered the statement "call Foobar". When I tried to execute the
macro (either using F5 or exiting control design mode and clicking the
button), I got the error "expect procedure, not variable" on the Call
statement.

Of course, I solved the problem. (The obvious solution is to change
only Caption, not Name. But before I realized that, I simply renamed
the Module1 macro.) But I'm curious: why did I get the error in the
first place?

Apparently, there is a variable with the same name as the ActiveX
control button. But I don't see it. How can I see it? What is its
purpose?

Finally, what is the "preferred" approach to do what I wanted and why,
namely: to have a control button on the worksheet to execute a macro
(pre-existing or not)?

 

[Jim Thomlinson]

Good question. The controls off of the forms toolbar are more like pictures
of controls. Sort of like a picture of a car. It looks just like a car but
you can not drive it and you can not get it repainted. It is a built in part
of XL. There are times when it is handy and times when it is not. More times
not. They are a little handyer for those who record macros as you can just
link the button to the macro. It is also handy if you want to put a button on
a bunch of different sheets all accessing the same macro.

Buttons from the control toolbox are the same (approximately) as the buttons
that you get if you program in VB6. The have properties and methods associted
with them. The properties can be esialy changed when you creat the button by
right clicking and selecting properties. You can also change the properties
on the fly using code. Because of this flexibility I tend dto use them more.
Additionally they have more events than just click. They can react to double
click, drag over and ...

The most likely reason that you got an error is that the button from the
control toolbox is an object. If I create a command button it will have the
name "CommandButton1". In code I could doe something like this...

Private Sub CommandButton1_Click()
CommandButton1.caption = "Tada"
End Sub

So if I have a procedure called
Sub CommandButton1()

End Sub
It may not like that...

 

[curiousgeorge408]

The most likely reason that you got an error is that the button
from the control toolbox is an object.
[....]
   CommandButton1.caption = "Tada"

Well, duh! Makes good sense. Thanks.