Adding ActiveX Controls to Worksheet in VBA

To add controls from the Control Toolbox (ActiveX controls) in VBA, I use the Add method of the OLEObjects collection. The Add method has a lot of arguments, but I only use a few, namely Class, Top, Left, Width, and Height.

Every month I create a billing worksheet. I have an external data table of all my open jobs which I refresh. Then I add a checkbox next to each job. I check the jobs that I will be billing that month and run another procedure that hides any jobs that are unchecked. The example below does basically the same thing. For the example, I use the Employees table from Northwind.mdb.

Createcheck1

First I delete any existing checkboxes. Since I know there’s no other OLEObjects on this sheet (CommandButtons, ListBoxes, etc.) I don’t discriminate when I delete. If there were some other types of OLEObjects that I wanted to keep, I would need to check

to only get the checkboxes.

Then I update my external data table making sure that the BackgroundQuery argument is false. You don’t want to start adding checkboxes until the whole range is there.

Finally, I add the checkboxes for every row, skipping the first one (the headers). The class name for the checkbox is Forms.Checkbox.1. Some other class names of note are

  • Forms.Combobox.1
  • Forms.Optionbutton.1
  • Forms.Textbox.1
  • Forms.Listbox.1
  • Forms.Commandbutton.1

I set the positional arguments to cover the cell in column A. Since my top and height will be the same as the loop index cell, I don’t bother to use Offset for those arguments. This will cover the entire cell and hide the TRUE and FALSE from linking the checkbox.

I set some other properties like Value to start with them all unchecked, LinkedCell so I can test the cell value later when I hide rows, and Caption because a caption would be superfluous. I’m creating a general OLEObject even though I specify the class. Some properties apply to this general object and some to the specific CheckBox object. For those that apply to the CheckBox object, I use the .Object property to get to it. How do I know? I guess, then when I get a run time error, I use the opposite of my guess. I’ve gotten pretty good at guessing though.

Once I’ve checked the rows that I want to keep, I use this procedure to hide the unchecked rows.

This just loops through the cells in column A that are adjacent to the external data table and hides the row if it’s False – skipping the header row again.

Createcheck2

16 thoughts on “Adding ActiveX Controls to Worksheet in VBA

  1. Here’s a simpleton’s simple approach:

    Type an X in column A of each row you want to display, and use the autofilter to hide rows with blanks in column A.

    Not a lot of VBA, but it still works pretty well, eh?

    – Jon

  2. I think it’s a great suggestion. As Jon says in your example you could just use an X in column A but if you’re doing something for someone else a set of check boxes looks a lot more “professional”. It’s certainly an idea I’m going to file away for future use (like a lot of the stuff you come up with on this blog).

  3. I find that when I add a scroll bar to a sheet using OLEObjects.Add, I loose my context. I.e., all variables in the VBA code are reset.

    Have you experienced this?

  4. I need your help again. Can you help me pls?

    Image size appears in button(msocontrolbutton) of custom toolbar Excel 2000 is very small and blurred using below code. How I can increase size of image in button (msocontrolbutton) as well as brightness of image in custom toolbar of Excel 2000 using VBA?

    It would be really great help for me……

    My code:
    ‘========================================

    Const cImgCommandBarID As String = “TMC Img Toolbar”

    ‘Image code

    Dim cbImgBar As CommandBar

    Set cbImgBar = CommandBars.Add(Name:=cImgCommandBarID, Position:=msoBarTop)

    ‘cbImgBar.context = 1000

    Dim cbImage As CommandBarControl

    Dim imgTool As Shape

    Dim sFileName, ImgSheet

    sFileName = ActiveWorkbook.Path & “ImagesABC.jpg”

    Const sNAME = “MyToolFace”

    ‘================

    ‘ hide appearance of picture from the user

    Application.ScreenUpdating = False

    ‘ insert picture from file and copy inserted picture

    ActiveSheet.Pictures.Insert(sFileName).CopyPicture Appearance:=xlScreen, Format:= xlBitmap

    Set cbImage = cbImgBar.Controls.Add(Type:=msoControlButton)

    With cbImage

    ‘.FaceId = 198

    .Caption = “TheMarketsImage”

    .Style = msoButtonIcon

    .Width = 120

    .Height = 100

    .PasteFace

    End With

    ‘ remove the inserted picture

    ActiveSheet.Pictures(ActiveSheet.Pictures.Count).Delete

    Application.ScreenUpdating = True

  5. Could you tell me how to rename the controls that adding in VBA ? I’ve try in 2 way:
    1) Sheet1.OLEObjects.Add(name:=”newName” ….
    2) .object.name=”NewName”
    But fail.

    Thx

  6. Sub test()
       
        Dim ole As OLEObject
       
        Set ole = Sheet1.OLEObjects.Add(“forms.commandbutton.1”)
        ole.Name = “newName”
       
        Set ole = Nothing
       
    End Sub
  7. I can’t figure out how to allow only one selection out of four checkboxes (like on a survey) using Excel. Is it possible?

  8. Shelita,

    Option Buttons are good for only allowing one selection. However, you could put VBA code in each check box that clears the other ones.

  9. I have a macro that is running, now I copy a set of rows from one sheet to other and add
    a check box to the newly copied cells.

    However the macro that is running exits!! Have any clues why?

  10. Further to my previous comment, I add the check box using the code dynamically and not after getting into design mode!

  11. Hello. Will this vba code creating an Active X control work in XL 2007? If so would you be willing to write an example in VBA code to delete them?

    Would you be additionally kind and write an example of VBA code that will fill these active x controls with VBA event procedures? ie check boxes

    I can interpret VBA and manipulated it farely well but writing it is a little more difficult.

    Thank you,

    Tom C.

  12. I’ve a chart with plan and actual progress in % (Y-Axis) and date (X-Axis), is there a way to add an automatic display to show the current date (today)?

  13. This was awesome – Thanks.

    Form checkboxes were driving me mad, but after adapting your functions for OLE checkboxes it all fit into place.
    I also set the following attribute so that autofilter and autosort would not mess up positions.

    .Placement = xlMoveAndSize

  14. Hello, I’ve added an activeX check box function to a cell in excel. When clicked in successful makes a number of other cells in that row change to what i want them to be.

    The big question – how do i now drag that activeX so there is one in every row that will do the same thing for that row? I’ve got well over 1,000 rows…

  15. Jo: There isn’t a way to drag and fill those controls. They live in a layer on top of the cells so they don’t behave like worksheet functions.

    You can copy and paste them and then position them where you want. Or you can write code to create them. Here’s a post on how to add ActiveX controls.

    http://dailydoseofexcel.com/archives/2004/10/06/adding-activex-controls-to-worksheet-in-vba/

    And here’s a post about how to respond to their events without having to write an event procedure for each one.

    http://dailydoseofexcel.com/archives/2005/08/23/toggle-button-events-class/


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.