How To: Assign a Macro to an ActiveX Control

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.

Hello World

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.

Controll Toolbox

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.

Command Button

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.

 

Peltier Tech Chart Utility

Comments

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

  2. 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.TextBox

    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.

    Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      MsgBox MyText.Name
    End Sub

    Now you need to activate the textboxes. Put this into the declarations section of a regular module:

    Dim TheTextBoxes() As New CText

    and 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 Sub

    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.

  3. 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).

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

  5. “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.

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

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

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

  9. 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 Sub

    Workbook at https://docs.google.com/leaf?id=0B1hgC5lSuLjVZGEwZmRhNGYtNDA2NS00MWU4LThjYjQtMmI5MGFjMTBhYjYy&hl=en

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

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

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

  13. Use a regular button, not a checkbox. When the user clicks on it, change all the true linked cells to false.

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

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

  16. 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! :)

  17. Gail –
    You should find a PowerPoint site to answer this. Try searching for PowerPoint questionnaire.

  18. 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?

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

  20. 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.”

  21. The Checkbox_Click event runs whenever the checkbox value is changed.

  22. 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?”

  23. You could declare a public variable in the Declarations section of a regular code module:

    Public gbDidNotClick As Boolean

    Then in your calling code:

    Sub ViewCheckbox()
        gbDidNotClick = True
        Sheets(“Sheet1″).OLEObjects(“myCheckBox”).Object.Value = 1
        gbDidNotClick = False
    End Sub

    And finally, in the control event code:

    Private Sub myCheckBox_Click()
        If Not gbDidNotClick Then
            MsgBox “Checkbox code is running.”
        End If
    End Sub
  24. Excellent, I’ll give that a shot. Thanks

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

    Sub TIME()
    '
    ' TIME Macro
    ' CURRENT TIME
    '
        Selection.InsertDateTime DateTimeFormat:="HH:mm", InsertAsField:=False, _
            DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
            InsertAsFullWidth:=False
    End Sub
  26. 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.

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

  28. It would probably take me at least six hours to figure out the answer in Word.

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

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

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

    Private Sub Worksheet_Activate()
        With Me.ListBox1
            .Clear
            .AddItem "Field_1"
            .AddItem "Field_2"
            .AddItem "Field_3"
            .AddItem "Field_4"
        End With
    End Sub
    
    Private Sub ListBox1_Click()
        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 xClear
        Worksheets("FieldData").Range("$A$1:$J$137").AutoFilter Field:=2, Criteria1:="1"
        Call xCopy
        Call xPaste
    End Sub
    
    Sub Field_2()
        Call xClear
        Worksheets("FieldData").Range("$A$1:$J$137").AutoFilter Field:=2, Criteria1:="2"
        Call xCopy
        Call xPaste
    End Sub
    
    Sub Field_3()
        Call xClear
        Worksheets("FieldData").Range("$A$1:$J$137").AutoFilter Field:=2, Criteria1:="3"
        Call xCopy
        Call xPaste
    End Sub
    
    Sub Field_4()
        Call xClear
        Worksheets("FieldData").Range("$A$1:$J$137").AutoFilter Field:=2, Criteria1:="4"
        Call xCopy
        Call xPaste
    End Sub
    
    Sub xClear()
        ' Clear Report Area
        With Worksheets("Dashboard")
           .Range(.Range("F11"), .Range("F11").End(xlToRight).End(xlDown)).ClearContents
        End With
    End Sub
    
    Sub xCopy()
        ' Copy the data
        With Worksheets("FieldData")
            .Range(.Range("A2"), .Range("A2").End(xlToRight).End(xlDown)).Copy
        End With
    End Sub
    
    Sub xPaste()
        ' Paste the data
        
        '' I think you can replace this...
        'Worksheets("Dashboard").Select
        'Range("F11").Select
        'ActiveSheet.PasteSpecial
        
        '' ... with this
        Worksheets("Dashboard").Range("F11").PasteSpecial
        
        Application.CutCopyMode = False
    End Sub
    
    '' you could replace xCopy and xPaste with this,
    '' and it would be easy enough to also clear the target range as well:
    Sub xCopyPaste()
        ' Copy the data
        With Worksheets("FieldData")
            .Range(.Range("A2"), .Range("A2").End(xlToRight).End(xlDown)).Copy _
                Worksheets("Dashboard").Range("F11")
        End With
    End Sub

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.