How To: Assign a Macro to an ActiveX Control
by Jon Peltier
Thursday, March 13th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In the latest post, I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also be run from the ActiveX controls on the Control Toolbox.
We’ll use this simple macro for this example.
Sub HelloWorld() MsgBox "Hello, World! ", vbExclamation End Sub
When it is run, we see a simple message.
Control Toolbox ActiveX Controls
Forms toolbar controls have been part of Excel since (I think) Excel 4. They are stable and easy to use. And they are old-fashioned. The Control Toolbox has newer ActiveX controls, introduced with Excel 97. These are fancier controls, with more formatting options, and somewhat more dynamic behavior. They sometimes are blamed for erratic behavior, and seasoned Excel developers generally prefer the Forms controls. However, these controls can provide some slick effects.
To use Control Toolbox controls, you need to make the Control Toolbox visible (actually it’s a toolbar). Use the same techniques as shown to make the Forms toolbar visible, or click the Control Toolbox button on the Visual Basic toolbar.
Click the Command Button button, and drag a rectangle in the worksheet where you want the button. No Assign Macro dialog pops up, however, and when you right click on the button, the context menu has no Assign Macro element either.
ActiveX controls work a bit differently. When they are clicked, or interacted with in other ways (e.g., when scrollbars are scrolled, when items in a combobox are selected, etc.), they raise events, which VBA can respond to. Instead of the missing Assign Macro item, click on View Code in the context menu. This inserts an event procedure in the code module behind the worksheet containing the button.
You can write code within this event procedure that runs whenever CommandButton1 is clicked. Note the two dropdowns at the top of the code module. The left dropdown shows CommandButton1, and if there were other ActiveX controls on the worksheet, they would also be listed when this dropdown is pulled down. The right dropdown lists the events which the selected control in the left dropdown responds to.
It is the wealth of these events which make ActiveX controls more powerful, and also perhaps a little flaky. But no rants now, this is a family blog.
To run the HelloWorld macro when the command button is clicked, call it from within the CommandButton1_Click event procedure. To do that, simply type the name of the macro, HelloWorld, within the event procedure.
Related Posts:
- Forms Controls and ActiveX Controls in Excel
- How To: Assign a Macro to a Button or Shape
- How To: Assign a Macro to a Toolbar or Menu
- Sample Parallel Coordinate Chart
- How To: Record Your Own Macro
- Dynamic Chart using Pivot Table and VBA
Posted: Thursday, March 13th, 2008 under VBA.
Comments: 11
Comments
Comment from Jeff Weir
Time: Wednesday, March 23, 2011, 7:58 pm
Hi Jon. I’ve posted the following question over at http://www.excelguru.ca/forums/showthread.php?42-How-do-you-reference-the-name-of-an-activex-control-within-the-control-s-change-event&p=122#post122 but I thought I’d post it here too. Apologies for the cross post.
Do you know whether you can return the name of an activex control within that activex control’s change event?
I’ve got some activex textboxes in a sheet with some default text in them. Once the user clicks on a particular textbox, I want to clear the default text in that textbox so that the user can add their own text without having to first select the existing text and then delete it.
So I’m using this:
Code:
Private Sub TextBox1_GotFocus()
If Sheet1.TextBox1.Text = Range(“TextBox1_default”).Value Then Sheet1.TextBox1.Text = “”
End Sub
But because I’m doing this with a lot of text boxes, I want to have a function that does this, that automatically gets passed the name of the calling activex object.
But I can’t work out how to pass the activex name/details to the function, and how I would dim the activex name/details within that function?
While something like application.caller works fine for a forms control, It doesn’t seem to work for an activex control: I’m using _GotFocus and _Change events to run the code. But for some reason, Application.Caller or ME does not seem to return details of the activex control that raised the event. I’ve tried these variations
* If I try application.caller in the _GotFocus event sub, I get “Type mismatch” error
* If I try application.caller.name in the _GotFocus sub, I get a “Object Required” error
* If I try With Me / MsgBox .Name / End With (with “/” denoting new lines) I get a very unhelpful “Sheet1″ returned
* Same goes for With Application.Caller / MsgBox Name / End With
I could replace all my activex controls with form controls, or I could forget about trying to ‘functionize’ my code, and just write seperate routines for each activex contlol. Or perhaps there’s another solution.
Any ideas?
Regards
Jeff
Comment from Jon Peltier
Time: Wednesday, March 23, 2011, 8:52 pm
Jeff -
Here’s how to do that. You need to set up a class for the textboxes. You don’t get as many events in the class as in the worksheet class, but you get enough.
Insert a new class module, call it CText. After “Option Explicit” at the top, add this:
Public WithEvents MyText As MSForms.TextBoxChoose MyText from the left hand dropdown atop the module, then click on Enter in the right hand dropdown. You get an Event procedure like the following. I’ve inserted the MsgBox just to show that it worked and could identify which textbox was clicked on.
Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox MyText.Name
End SubNow you need to activate the textboxes. Put this into the declarations section of a regular module:
Dim TheTextBoxes() As New CTextand insert these procedures:
Sub ActivateActiveXTextBoxes()
Dim sh As Shape
Dim iTextBoxCount As Long
ReDim TheTextBoxes(1 To 1)
iTextBoxCount = 0
For Each sh In ActiveSheet.Shapes
If sh.OLEFormat.Object.OLEType = xlOLEControl Then
iTextBoxCount = iTextBoxCount + 1
ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
Set TheTextBoxes(iTextBoxCount).MyText = sh.OLEFormat.Object.Object
End If
Next
End Sub
Sub DeactivateActiveXTextBoxes()
Dim iTexts As Long
On Error Resume Next
For iTexts = LBound(TheTextBoxes) To UBound(TheTextBoxes)
Set TheTextBoxes(iTexts).TheText = Nothing
Next
End SubRun the activation procedure, then click in a textbox. The message box will tell you which one it is, and you can follow up with whatever you need to do.
Comment from Jeff Weir
Time: Wednesday, March 23, 2011, 9:27 pm
THanks Jon. For all my questions you’ve answered over the last 2 years I owe you a night on the town in Wellington NZ, at the very least! Redeem at your leisure. (Truth be known, I probably owe you the airfare here, too).
Comment from Jeff Weir
Time: Thursday, March 24, 2011, 4:23 am
Note that I was getting “Object doesn’t support this property or method” on the line If sh.OLEFormat.Object.OLEType = xlOLEControl Then in the ActivateActiveXTextBoxes sub.
This had me scratching my head for a while. Finally tracked it down to having some shapes in the sheet that were not ole objects. So I added an IF wrapper If sh.Type = msoOLEControlObject Then
One thing I don’t follow is what is going on in the right hand side of the equality in this line: Set TheTextBoxes(iTextBoxCount).MyText = sh.OLEFormat.Object.Object. Object.object?
Finally, is there any improvement you’d suggest for the ‘business end’ procedure (i.e. ‘point’) of all this:
Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
On Error Resume Next
If MyText.Text = Range(MyText.Name & "_Default") Then MyText.Text = ""
On Error GoTo 0
End Sub
Thanks again, Jon
Comment from Jon Peltier
Time: Thursday, March 24, 2011, 10:26 am
“Object.Object” – well, it’s what works. If you look in the Object Browser, the OLEFormat has an Object member, which is the OLEObject. OLEObject in turn also has an Object member, which in this case is the textbox.
What I would do is select the entire contents of the textbox when it gains focus, so the user can change his mind and not delete it, or change one character in it, or whatever. With it all selected, typing replaces the text, as if it were deleted.
What I would really do is try to make the interface work using cells, not controls.
Comment from Jeff Weir
Time: Thursday, March 24, 2011, 2:10 pm
Very good idea re selecting the entire textbox.
I’m using textboxes because I wanted to escape the ‘grid’ for a form I’m putting together. http://screencast.com/t/5mYAEhDgAZX
I thought I’d have more chance of getting people to fill in a form properly if it looks just like a ‘bought one’.
So far my favorite part of the form is the ’007′ reference at the top left.
Comment from Pieter
Time: Tuesday, April 12, 2011, 1:51 am
HI Jon
I need to create a “configurator” where I have 5 ActiveX Control Pictures. If you click on the first picture, it must give me 4 boxes.
1st box – must be a box that have general info in it about the item in the picture.
2nd, 3rd & 4th box- must bring up a box that several items can be selected and later carried over to another sheet that can be printed out.
Then there must be a clear button to clear all 4 box and only leave the 5 pictures there. then when you click on another picture the 4 boxes must appear with info about that specific item in the picture.
THen after everything has been selected there must be a Quote button that take all the selected data to another sheet to be compiled into a Quote sheet.
Can all this be done??
Great site!
Pieter
Comment from Jon Peltier
Time: Thursday, April 14, 2011, 10:16 am
Pieter -
Could you use regular inserted pictures? You can assign each to a macro that shows the relevant text boxes. Clear and Compile buttons can be done in the same way with Forms menu buttons, and macros assigned to them.
Comment from Jeff Weir
Time: Thursday, April 14, 2011, 4:53 pm
Jon: Here’s a very sneaky way to do this from Sam (one of my fellow participants on the Excel Hero course, and a regular on some of the boards). He puts a tranparent textbox over the top of the activex controls, so that someone trying to select the activex control inadvertantly selects the textbox, which then uses application.caller to pass the name of the calling textbox to the activex control with the same name. Ingenious! Sneaky!
Sub FinCtrl()
Dim tBox As Object ' ActiveX
Dim tShp As Shape ' Textbox
Set tBox = Sheet1.OLEObjects("Text" & Application.Caller).Object
Set tShp = Sheet1.Shapes(Application.Caller)
' Perform your action on the text Box clicked
tBox.Text = "Hello From" & "Text" & Application.Caller
tShp.Visible = msoFalse
MsgBox "yea"
End SubWorkbook at https://docs.google.com/leaf?id=0B1hgC5lSuLjVZGEwZmRhNGYtNDA2NS00MWU4LThjYjQtMmI5MGFjMTBhYjYy&hl=en
Comment from Pieter
Time: Friday, April 15, 2011, 1:09 am
HI Jon
Ive tried it with inserting a regular picture. What must I put in the macro to attach it to the regular picture? I’ve tried to record a macro, copy a text box and then run it, but then it just flickers and i dont see anything. Ive tried to record and then insert a tect box, copy text into it and then run it, but then it flickers and gives me the last text I typed? I am not that clued up with macros, but my boss requested this, and I do not know how to create something that works and look professional
Hope someone can help me with this.
Comment from Jon Peltier
Time: Saturday, April 16, 2011, 10:15 am
Pieter -
As described in How To Assign a Macro to a Button or Shape:
Right click on the inserted picture, choose Assign Macro from the popup toolbar, and select the macro from the Assign Macro dialog.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.