CODEWITHSUNDEEP
excellabelvba
melculetz
melculetz

Reputation: 1971

How can I dynamically add a radio button on a form using VBA

I want to dynamically add a radio button on a form, using VBA.

I tried writing this code, but it crashes with 'Type Mismatch'

Dim optionBtn As OptionButton
Set optionBtn = UserForm1.Controls.Add("Forms.OptionButton.1", "name", True)

optionBtn.Left = 10
optionBtn.Top = 10
optionBtn.Width = 30
optionBtn.Group = "q1"

I also tried doing this:

Dim optionBtn As Control
Set optionBtn = UserForm1.Controls.Add("Forms.OptionButton.1", "name", True)

optionBtn.Left = 10
optionBtn.Top = 10
optionBtn.Width = 30
optionBtn.Group = "q1"

but I get a Control, not a OptionButton - how can I cast it to a OptionButton ? (sorry, I'm new to VB)

Upvotes: 1

Views: 19428

Answers (4)

Joshua Matisoff
Joshua Matisoff

Reputation: 11

Actually, I believe your problem lies in the fact that you are naming optionBtn as an object button. It needs to be named as a MSForms Option Button. Since a Variant can be an object, it will work when using a variant.

I used the following and it works fine.

Dim TempForm As Object
Dim newOptionButton as MSForms.OptionButton
Dim sUserformName as string
Dim i as integer
Dim x as integer
Dim y as integer
' other junk removed for example sake...


sUserformName = sheet1.cells(1,1)

' create form
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With TempForm
    .Properties("Caption") = sUserformName
    .Properties("Width") = 450
    .Properties("Height") = 300
End With

for i = 3 to sheet1.range("A65536").End(XlUp).Row
sDefault = sheet1.cells(i,5)
iGN = sheet1.cells(i,6)


' additional code removed... for sake of example... the code would add labels, text boxes, etc...

    Set newOptionButton = TempForm.designer.Controls.Add("Forms.optionbutton.1")
    With newOptionButton
        .Caption = sDefault
        .GroupName = "Group" & iGN
        .Top = 20 + (20 * x)
        .Left = y
        .Height = 16
        .Font.Size = 8
        .Font.Name = "Ariel"
    End With

' here the code changes x and y depending on where the user (excel template) directs the next control.

next i

Good luck....

Upvotes: 1

Andy Pope
Andy Pope

Reputation: 141

You need to define the object as an optionbutton from the msforms library.

Dim optionBtn As MSForms.OptionButton
Set optionBtn = UserForm1.Controls.Add("Forms.OptionButton.1", "name", True)

Upvotes: 0

guitarthrower
guitarthrower

Reputation: 5834

mark's code should work, but I often prefer to create the item manually then show/hide it based on the need.

Upvotes: 0

Mark Biek
Mark Biek

Reputation: 150759

I was able to get it work with this (Excel 2003):

Dim lbl As Variant

Set lbl = UserForm1.Controls.Add("Forms.Label.1", "lblFoo", True)
lbl.Caption = "bar"

Update to reflect your change from a Label to an OptionButton

Again, the key is use a Variant type for the variable that you are assigning the returned control to:

Dim opt As Variant

Set opt = UserForm1.Controls.Add("Forms.OptionButton.1", "radioFoo", True)
opt.Caption = "Bar"

Keep in mind that autocomplete won't work on the variables that are defined as Variants. However you can still refer to properties and methods of those variables by typing them manually.

Upvotes: 2

Related Questions