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
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.
e=”color:#00007F”>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.
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
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).
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?
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 & “\Images\ABC.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
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
Dim ole As OLEObject
Set ole = Sheet1.OLEObjects.Add("forms.commandbutton.1")
ole.Name = "newName"
Set ole = Nothing
End Sub
I can’t figure out how to allow only one selection out of four checkboxes (like on a survey) using Excel. Is it possible?
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.
Thank you very much for these detailed instructions. Works flawlessly!