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

Question

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)

Mark Biek · Accepted Answer · 2010-01-12 16:44:26Z

2

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.

edited Jan 12, 2010 at 16:44

answered Jan 12, 2010 at 13:56

Mark Biek

152k54 gold badges160 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshua Matisoff · Accepted Answer · 2012-01-31 17:16:17Z

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....

guitarthrower · Accepted Answer · 2010-01-12 17:37:27Z

0

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

answered Jan 12, 2010 at 17:37

guitarthrower

5,9143 gold badges33 silver badges38 bronze badges

2 Comments

Mark Biek Over a year ago

It all depends on what you're trying to do. I've had situations where the form displayed changed depending on the information in the spreadsheet. In that case, dynamic control creation is pretty much the only way to handle it.

guitarthrower Over a year ago

I agree. I would hate to have to create them all the time if I only needed to unhide... It was hard to tell from the question the where the OP was coming from. As long as we're throwing options out there... I will also use Multi-page tools and hide the tabs to control form flow.

Andy Pope · Accepted Answer · 2010-01-13 11:52:54Z

0

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)

answered Jan 13, 2010 at 11:52

Andy Pope

1412 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related