Trouble creating a UserForm programmatically

I am having issues with writing code that creates a UserForm on Excel 2013. I have searched Microsoft help sites as well as many other sites for solutions, and I have figured out that I don't have a problem with the coding, but it is a problem that I have no idea how to solve.

I found a MS help website that walked me through how to create a UserForm using VBA code. It said to first click tools > references > check MS VBA Extensibility. I then copy and pasted their sample code, but it gave me the error: Run-time error '1004': Application-defined or object-defined error. I then went back to references and saw that MS VBA Extensibility's location is given as C:\Program Files (x86)\Common Files\Microsoft Shared\VBA. I went into the MS Shared folder, but didn't find VBA, so I copied it to my computer from another computer in my office. That did not fix the problem. I am wondering if I may be missing files? The only folder inside VBA is VBA6 which contains only VBE6EXT.OLB. I have tried running code that claims to create a UserForm, but I keep getting the same error. Here is an example of the code I have tried using.

Sub Add_Form1()

' Declare a variable to hold the UserForm.
Dim x As Object

' Create a new UserForm. You can use this new VBComponent object
' to manipulate the User Form.
Set x = Application.VBE.ActiveVBProject.VBComponents.Add _
(vbext_ct_MSForm)

End Sub

My error is on the line Set x = Application.VBE.....
When I manually type in the code, VBA recognizes everything I am typing in (it will give me autofill options), so I am stumped as to why this code won't work for me.

Eh, I've got to ask.

Why do you want to create a userform programmatically?

Anyway here's a (bad?) example of how to do it.

You have to trust access to the VBA project in the macro settings in Trust Center.

If you need a varying number of checkboxes, you can create a Userform (at design time (now)) with a multi-select listbox whose .Style is set to fmListStyleOption.

For a crude example, create a user form with a ListBox1 and put this code in a normal module.

Please Login or Register  to view this content.

I have a couple more questions about Mike's code. What does "Rem can be set at design time" mean? Am i supposed to replace that text with something? Also, how do I set the row source to a range that is selected based on the user's input? I have two variables, Cell1 and Cell2, that are used to select a range. I tried using (under With ListBox1) .RowSource = Range(Cell1,Cell2) but I get a type mismatch error.

It's a comment telling you that you can set the properties at design time rather than at run time in the code.

As for the source for the listbox, how does the user input determine what that is?

Will the user input/select an order when they open the form?

If you have code that selects the relevant range then it should be straightforward to populate the listbox.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Originally Posted by Mitch319

... What does "Rem ..." mean? ...

Rem is an old, deprecated indicator that the line that follows is a comment.

Why not store the data you want in an array then use that to populate the listbox?