VBA Dynamically add option button to userform

Question

I have this code to add an option button to a user form from https://analysistabs.com/vba-code/excel-userform/optionbutton/ which seems simple enough, but it gets an error "Type mis-match".

The userform has been added from Insert>UserForm in the editor. The editor only gives one possible option in the statement "Dim OpBtn as OptionButton". So what could go wrong?

    Sub Add_Dynamic_OptionButton()

    Dim OpBtn As OptionButton

        'Add Dynamic OptionButton and assign it to object 'OpBtn'
        Set OpBtn = UserForm1.Controls.Add("Forms.OptionButton.1")  
             'ERRORS HERE

        'Assign OptionButton Name
        OpBtn.Caption = "Dynamic OptionButton"

        'OptionButton Position
        OpBtn.Left = 486
        OpBtn.Top = 48

    End Sub

I'd like to use this code to add an option button. Thanks in advance

You'll need to qualify your reference Dim OpBtn As msforms.OptionButton. — user6585761
– user6585761, Commented Sep 12, 2019 at 15:46

Mathieu Guindon · Accepted Answer · 2019-09-12 15:59:11Z

2

If you bring up the Project References dialog (from the Tools menu), you'll see the Microsoft Excel type library listed before the MSForms type library - and the sorting order is important.

When a data type isn't qualified with the library it's from, VBA uses the reference priority (i.e. the sort order) to determine who wins when two or more libraries define different types that have the same name. Excel.OptionButton is a thing, as is MSForms.OptionButton - but since the Excel library has higher priority (and can't be moved down since it's your host application), then OptionButton will bind to Excel.OptionButton.

But UserForm1.Controls.Add("Forms.OptionButton.1") yields a MSForms.OptionButton - hence the type mismatch.

Fully qualify your data types, and the problem will solve itself.

Dim OpBtn As MSForms.OptionButton

answered Sep 12, 2019 at 15:59

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

4 Comments

Chas Over a year ago

Great explanation. The code is in a module, not in the code behind the form. I made the change, but it still does not work for me. The code does run. If it is creating a button, I don't know where it is going. UserForm1 is a form in the same file as the module with the code. There are no other open files. I added OpBtn.Visible = "true", without success.

Mathieu Guindon Over a year ago

"If it is creating a button, I don't know where it is going" is a strong hint that you're working with a default instance of the form. Does any thing unload it? Read the article I linked above: you want to consistently work off a New instance.

Mathieu Guindon Over a year ago

@Chas ...and add the buttons on every new instance of the form.

Chas Over a year ago

Got it working on new instance as suggested. Thanks.

Collectives™ on Stack Overflow

VBA Dynamically add option button to userform

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related