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

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.

Subscribe without commenting

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

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

 

Site by: Dawud Miracle, Business Coach & WordPress Websites