add checkbox to Excel UserForm at design time using vba

Question

When I try to add checkboxes to a UserForm in Excel at design time using the following VBA code I get:

"Run time error '13': Type Mismatch"`

At the statement Set cb = UFvbc.Designer.Controls.Add("Forms.CheckBox.1").

Sub Add4Controls()
   Dim UFvbc As VBComponent
   Dim r As Long

   Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForm1")

   'Add 4 CheckBoxes
   Dim cb As CheckBox 
   For r = 1 To 4
      Set cb = UFvbc.Designer.Controls.Add("Forms.CheckBox.1")
      With cb
        (some statements to set .Width, .Height, .Left, .Top, and .Caption)
      End With
   Next r
End Sub

But if I change:

Dim cb As CheckBox to
Dim cb As CommandButton

And

Set cb = UFvbc.Designer.Controls.Add("Forms. CheckBox.1") to
Set cb = UFvbc.Designer.Controls.Add("Forms. CommandButton.1")

Then I get 4 CommandButtons in a column as expected. Although cb is “False”, which was unexpected.

I can get the checkboxes by dropping “As CheckBox” from the declaration for cb and making it Variant, but it irritates me that I need to do that (and I’ll never admit how long it took me to discover that), and I don’t understand why it works with CommandBar but not Checkbox. What is it that I don’t understand?

Vityata Vityata · Accepted Answer · 2018-02-12T09:13:15

This is an example of your code adding 4 checkboxes:

Option Explicit

Sub Add4Controls()
    'Microsoft Visual Basic Applications Extensibility 5.3
    Dim UFvbc As VBComponent
    Dim r As Long

    Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForm1")
    Dim cb As Control
    For r = 7 To 10
        Set cb = UFvbc.Designer.Controls.Add("Forms.CheckBox.1", "CheckBox" & r, True)
        With cb
            Debug.Print cb.Name
        End With
    Next r    
End Sub

As a side note - whenever you are using external library, as in this case, make sure that you mention it.

add checkbox to Excel UserForm at design time using vba

1 Answers