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 often are blamed for erratic behavior, and only work in Windows versions of Excel. Seasoned Excel developers generally prefer the Forms controls and avoid the ActiveX 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.
Jeff Weir says
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
Jon Peltier says
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:
Choose 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.
Now you need to activate the textboxes. Put this into the declarations section of a regular module:
and insert these procedures:
Run 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.
Jeff Weir says
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).
Jeff Weir says
Note that I was getting “Object doesn’t support this property or method” on the line
If sh.OLEFormat.Object.OLEType = xlOLEControl Thenin 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 ThenOne 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
Jon Peltier says
“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.
Jeff Weir says
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.
Pieter says
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
Jon Peltier says
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.
Jeff Weir says
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!
Workbook at https://docs.google.com/leaf?id=0B1hgC5lSuLjVZGEwZmRhNGYtNDA2NS00MWU4LThjYjQtMmI5MGFjMTBhYjYy&hl=en
Pieter says
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.
Jon Peltier says
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.
Amr El Khodary says
HI, I am doing a questionare that requires a user to select through checking an ActiveX Button. I want him when he checks a button, other buttons, if checked by mistake, to be unchecked..can you help advising how can I do that?
Many thanks
Appreciate your help
Jon Peltier says
Use a regular button, not a checkbox. When the user clicks on it, change all the true linked cells to false.
James Haughton says
I am trying to get a macro to work with a drop down form control so that when a selection is made it runs a macro to refresh all. All that I have tried so far never seems to work.
Jon Peltier says
James –
Right click on a drop down control from the forms menu, and you will see Assign Macro in the context menu. Select a macro that is written in a regular code module.
Gail says
I just want to know if how can i make an interactive questionnaire in powerpoint which it has multiple of correct answers, using option button. for example :
which of the following are green objects? Select 3 answers:
a. leaf
b. caterpillar
c. apple
d. mango
e. grass
please reply very soon. I really need it, thank you in advance! God bless! :)
Jon Peltier says
Gail –
You should find a PowerPoint site to answer this. Try searching for PowerPoint questionnaire.
bs0d says
How can I easily prevent an Active X checkbox from running code in the _click() sub when a user hasn’t actually clicked it?
Application.EnableEvents = FALSE doesn’t seem to work. And TypeName(Application.Caller) appears to return “Error” (meaning called by VBA) even when I click the box. I understand it should return “String” if called by a control. But maybe doesn’t apply to Active X controls?
Jon Peltier says
How would the code run if the user hasn’t clicked the checkbox? You mean if the linked cell was changed directly?
You could use a Form Control checkbox, and assign a macro to run when it is clicked on.
bs0d says
For some reason, my _click() code is executed when I set the .value for the OLEObject in a separate sub…
Example:
Private Sub myCheckBox_Click()
MsgBox “Checkbox code is running.”
end Sub
Sub ViewCheckbox()
Sheets(“Sheet1”).OLEObjects(“myCheckBox”).Object.Value = 1
End Sub
Execute ViewCheckbox() and you get the prompt, “Checkbox code is running.”
Jon Peltier says
The Checkbox_Click event runs whenever the checkbox value is changed.
bs0d says
Jon,
I understand this to be the case. That’s the basis of my question. “How can I easily prevent an Active X checkbox from running code in the _click() sub when a user hasn’t actually clicked it?”
Jon Peltier says
You could declare a public variable in the Declarations section of a regular code module:
Then in your calling code:
And finally, in the control event code:
bs0d says
Excellent, I’ll give that a shot. Thanks
ROBERT C says
I AM CURRENTLY TRYING TO CREATE A BUTTON THAT PLACES THE CURRENT TIME THE BUTTON WAS CLICKED. HOWEVER I AM HAVING MAJOR ISSUES. I WANT TO USE ONE BUTTON THAT WILL FILL A ROW WITH DIFFERENT TIMES SO WHEN IT IS CLICK IT WILL FILL COLUMN 1 ON THE TABLE WITH THE CURRENT TIME THAN LATER IF CLICKED IT WILL FILL COLUMN 2 ON THE TABLE AND SO ON. I WANT TO USE A COMMAND BUTTON BUT IF I HAVE TO USE REGULAR TEXT I WILL BUT IM SO STUCK. ANY HELP WILL BE GREAT.
Jon Peltier says
Your caps lock key is broken.
Does the button do anything else besides enter the time? If not, Ctrl+Shift+: enters the current time in the active cell.
ROBERT C says
Sorry about that im used to using it in the field i work in. always forget to turn it off.
it only puts in the current time however i am on word so it may be different than excel an i need it to be inserted into a table i have put in place. I want to be able to have my guys click the button and the time will show up in the next cell in the table is this at all possible. My guys arent taking the time for the other chortcuts
Jon Peltier says
It would probably take me at least six hours to figure out the answer in Word.
pike says
bit of a work around for a sheet with mixed form and avctiveX controls (Jeff Weir)
For Each sh In ActiveSheet.Shapes
If TypeName(sh.OLEFormat.Object) Like “OLEObject” Then
If TypeName(sh.OLEFormat.Object.Object) Like “TextBox” Then
iTextBoxCount = iTextBoxCount + 1
ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
Set TheTextBoxes(iTextBoxCount).myText = sh.OLEFormat.Object.Object
End If
End If
Next
JDS says
Jon,
I’m having problems with a ListBox ActiveX I placed on my 1st worksheet (called “Dashboard”). I have a 2nd page (called “FieldData”). I think I’ve populated it correctly, but I cannot get a single click on any ListBox item to run a Sub (macro) named after the ListBox item.
I’m past my deadline for submitting it to my boss and I need help!
[CODE]
Option Explicit
Private Sub ListBox1_Click()
Sheets(“Dashboard”).Select
With Dashboard.ListBox1
.AddItem “Field_1”
.AddItem “Field_2”
.AddItem “Field_3”
.AddItem “Field_4”
End With
End Sub
Sub ListBox1_new()
Call ListBox1_Click
Dim ListBox1 As Object
If ListBox1.Text = “Field_1” Then
Call Field_1
ElseIf ListBox1.Text = “Field_2” Then
Call Field_2
ElseIf ListBox1.Text = “Field_3” Then
Call Field_3
ElseIf ListBox1.Text = “Field_4” Then
Call Field_4
Else
End If
End Sub
Sub Field_1()
Call Clear
ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”1”
Call Copy
Call Paste
End Sub
Sub Field_2()
Call Clear
ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”2”
Call Copy
Call Paste
End Sub
Sub Field_3()
Call Clear
ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”3”
Call Copy
Call Paste
End Sub
Sub Field_4()
Call Clear
ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”4”
Call Copy
Call Paste
End Sub
Sub Clear()
‘ Clear Report Area
Sheets(“Dashboard”).Select
Range(“F11”).Select
Range(“F11:O22”).Select
‘ Range(Selection, Selection.End(xlToRight)).Select
‘ Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets(“FieldData”).Select
Selection.AutoFilter
End Sub
Sub Copy()
‘ Copy the data
Range(“A2”).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
Sub Paste()
‘ Paste the data
Sheets(“Dashboard”).Select
Range(“F11”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
It’s supposed to be pretty simple, click on the Field# in the ListBox. That Sub will filter the FieldData to that criteria,
copy the data, and paste it in a specified location on the “Dashboard” page. I want the user to be able to repeat for any Field#
one right after the other. I have built-in clearing functions (found above) to clear the specified location.
Can you help?
JDS
Jon Peltier says
1. This code goes into the code module behind the “Dashboard” worksheet.
2. You don’t want to populate the listbox within its own click event. How about in the Worksheet_Activate event? Also, clear it first, so you don’t get multiple blocks of the items you want.
3. Dashboard.ListBox1 does not reference anything valid, unless you’ve given your sheets code names. You could use Worksheets(“Dashboard”).Listbox1, but since the code resides behind the sheet, you can get by with simply me.Listbox1.
4. Don’t use keywords (Copy, Paste, Clear) for function or variable names. You’ll get confused and Excel might get confused.
5. You don’t want to be declaring a variable named Listbox1, because you already have an object with the name.
6. I think you want to filter what’s on “FieldData”, but probably “Dashboard” is the activesheet. Anyway, you don’t need to keep switching sheets.
I don’t have an autofilter on my “FieldData” worksheet, so I can’t really test this, but here’s some code that’s a lot closer to what you need. This code could also be streamlined further a great deal.