Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a control button to insert rows | Excel Discussion (Misc queries) | |||
Use of Forms Command-button on Worksheet | Excel Discussion (Misc queries) | |||
Hyperlink added to an activex control button | Excel Discussion (Misc queries) | |||
how do you use the ActiveX Calendar control in an Excel Worksheet. | Excel Worksheet Functions | |||
How do I create a button or control that will automatically go to | Excel Worksheet Functions |