I want to declare and create an User Form with VBA

Question

I'm trying to create a new user form in VBA with the help of:

Automatically creating checkboxes on a userform, fed from array

Dim SelectionForm As Control

For r = 1 To UBound(individualValues)
SelectionForm.height = UBound(individualValues) * 30
    Set c = SelectionForm.Controls.Add("Forms.CheckBox.1", "Checkbox" & r,True)
    With c
        .Caption = individualValues(r)
        .Width = 100
        .height = 20
        .Top = r * 20
        .Left = 10
    End With

Next r

SelectionForm.Show

But I'm stuck with foolowing error on Dim c as Control:

User-defined type not defined

My idea is to create a new user form on VBA and populate it with checkboxes (from an array, instead of a range as in the link).

The code is working when I use an existing user form, but I would like to share my code with others, and then it would be a bit akward to have them create a new userform named UserForm1 by hand - if you know what I mean.

So the main issue here is to declare a new user form in a macro.

I also wonder how this has worked with those people in the URL, as it's only from 2014 and it seems to have worked then. So I wonder if there's been an update that has discontinued a certain library or what's the issue here.

requires reference to the MS Forms library, or use late-bound `Dim SelectionControl as Object`. — David Zemens, Nov 03 '19 at 16:02
Also, it looks like `SelectionForm` is intended to be a `Control` type, not a UserForm. If your idea is simply to share this vba code and not the dependent form object(s), that's probably not a great way to implement it. Would be better to either distribute the XLSM file or an XLAM file (add-in) that contains all of the required components. — David Zemens, Nov 03 '19 at 16:03
And by User Form I mean any window that pops up and can have check boxes, of which values I can pass on to an array. — p_tu, Nov 03 '19 at 16:08
The simple answer to "can I add reference to MS Forms library in the macro" is no. Techincally it is yes, but you can't do that *and* use early-bound `As Control` declaration (well, you probably can also do this if you use conditional compilation...). This is why you distribute XLSM for XLAM files, rather than plain-text versions of code modules. The XLSM/XLAM file format will contain everything that you need for the procedures to execute (assuming those resources exist on the computer that is trying to run the macro). — David Zemens, Nov 03 '19 at 16:21
How to add reference programatically look e.g. [here](https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically) — Daniel Dušek, Nov 03 '19 at 16:38
Okay then, thanks for your help. So can I create the form with a late-binding object then? How can I do that? — p_tu, Nov 03 '19 at 16:38
Solutions that don't use the XLSM/XLAM approach will require additional user configuration to allow programmatic access to the VB object model. While possible as per the answer provided below, I'm not sure this is particularly favorable -- it's better, though not much better than asking user to create the blank user form manually.. It's still not a great way to *distribute* your code. It seems you're trying to distribute code without using the generally accepted practices for doing so. — David Zemens, Nov 04 '19 at 16:21
My main intention was to keep it simple so I could post the code as is and explain what it does, instead of actually focusing on spreading it easily. I got to the point where I had to allow Excel to enable access to Object models in the trust center (which was already pushing it) and was able to create a userform in the code itself. But the problem was that I couldn't then load the checkbox' values to an array, without adding a save button and assign an event to save in a new class module. So I have come to the conclusion that this was not a possible way to approach this. — p_tu, Nov 05 '19 at 08:44

barneyos · Answer 1 · 2019-11-03T18:06:35.070

Please analyze this simple code, it will help you I suppose

Sub MyForm()

    Dim myFRM   As Object
    Dim myFRM2  As Object

    ' If Userform exists in workbook and its name is UserForm1
    Set myFRM = UserForm1

    ' If You want to create userform on the fly
    Set myFRM2 = ThisWorkbook.VBProject.VBComponents.Add(3)

    Dim c As Object
    ' or
    Dim d As MSForms.CheckBox

    Set c = myFRM2.Designer.Controls.Add("Forms.CheckBox.1", "Checkbox1", True)
    Set d = myFRM2.Designer.Controls.Add("Forms.CheckBox.1", "Checkbox2", True)

    VBA.UserForms.Add(myFRM2.name).Show
    ThisWorkbook.VBProject.VBComponents.Remove myFRM2

    Set c = myFRM.Controls.Add("Forms.CheckBox.1", "Checkbox1", True)
    Set d = myFRM.Controls.Add("Forms.CheckBox.1", "Checkbox2", True)

    myFRM.Show


End Sub

I want to declare and create an User Form with VBA

1 Answers1