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.


Sub UpdateList()
Dim oCheck As OLEObject
Dim rCell As Range

'clear existing checkboxes
For Each oCheck In Sheet1.OLEObjects
Next oCheck

'update the external data
Sheet1.QueryTables(1).Refresh False

'add new checkboxes
With Sheet1.QueryTables(1).ResultRange
For Each rCell In .Columns(1).Cells
If rCell.Row > .Rows(1).Row Then
rCell.RowHeight = 14 'this makes the checkbox look nicer
With Sheet1.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
Top:=rCell.Top, Left:=rCell.Offset(0, -1).Left, _
Height:=rCell.Height, Width:=rCell.Offset(0, -1).Width)

.Object.Caption = ""
.LinkedCell = rCell.Offset(0, -1).Address
.Object.Value = False
End With
End If
Next rCell
End With

End Sub

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

If TypeName(oCheck.Object) = "CheckBox" Then

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.

Sub HideRows()

Dim rCell As Range

With Sheet1.QueryTables(1).ResultRange
For Each rCell In .Columns(1).Cells
If Not rCell.Offset(0, -1).Value And _
rCell.Row <> .Rows(1).Row Then

rCell.EntireRow.Hidden = True
End If
Next rCell
End With

End Sub

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.


14 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


    End With

    ‘ remove the inserted picture


    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” ….
    But fail.


  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

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see