lundi 12 février 2018

add checkbox to Excel UserForm at design time using vba

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?




Aucun commentaire:

Enregistrer un commentaire