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

Sub UpdateList()    
    Dim oCheck As OLEObject
    Dim rCell As Range
   
    'clear existing checkboxes
    For Each oCheck In Sheet1.OLEObjects
        oCheck.Delete
    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.

Createcheck2

14 Comments

  1. Jon Peltier says:

    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. Tony M says:

    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. Eric Horowitz says:

    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. Hudson says:

    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. Shelita says:

    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. Brett says:

    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. Jake says:

    Thank you very much for these detailed instructions. Works flawlessly!

  10. DK says:

    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?

  11. DK says:

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

  12. Tom says:

    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.

  13. Steve says:

    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)?

  14. Enos says:

    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

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: