Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 15th 08, 11:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 85
Default How to create control button on worksheet: Forms v. ActiveX feature?

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)?

  #2   Report Post  
Old February 16th 08, 01:13 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,941
Default How to create control button on worksheet: Forms v. ActiveX featu

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...
--
HTH...

Jim Thomlinson


" wrote:

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)?

  #3   Report Post  
Old February 16th 08, 06:32 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 85
Default How to create control button on worksheet: Forms v. ActiveX featu

On Feb 15, 5:13*pm, Jim Thomlinson wrote:
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create a control button to insert rows nadswilkes28 Excel Discussion (Misc queries) 4 May 15th 07 04:20 AM
Use of Forms Command-button on Worksheet Jim May Excel Discussion (Misc queries) 12 November 13th 06 06:08 PM
Hyperlink added to an activex control button Dimmer Excel Discussion (Misc queries) 1 August 11th 05 03:06 PM
how do you use the ActiveX Calendar control in an Excel Worksheet. EricF Excel Worksheet Functions 0 November 10th 04 09:39 PM
How do I create a button or control that will automatically go to Roger Excel Worksheet Functions 1 October 29th 04 01:31 AM


All times are GMT +1. The time now is 02:40 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017