Creating form programmatically in the module using vba

Question

I want to create a user form in the module using VBA programmatically. I am a novice and inexperienced so I have tried couple of examples, but they are not fulfilling my requirements.

I just want macro that

creates a user form within a module using VBA
has a ListBox with some data
has a CommandButton with a listener

Here is the code which I used

Option Explicit

Sub MakeuserForm()
'Dim CommandButton1 As MsForms.CommandBarButton
'Dim ListBox1 As MsForms.ListBox
Dim UserForm1 As VBComponent

Set UserForm1 = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
With UserForm1
.Properties("Height") = 100
.Properties("Width") = 200
On Error Resume Next
.Name = "My Form"
.Properties("Caption") = "This is your user form"
End With
ShowForm
End Sub

Sub ShowForm() 
NewForm.Show 
End Sub

Now I don't know how to add ListBox and button to the form with a listener.

Post what you have tried and please read the FAQ's http://stackoverflow.com/faq. This is a not place for obtaining quick answers it is a place for learning. — Alistair Weir, Jul 17 '12 at 09:48
Creating whole forms dynamically like this usually doesn't work out as well as you would think. Unless you really are unable to make a generic version, at least some sort of skeleton, this is going to be a whole heck of a lot of unnecessary work. — Brad, Jul 17 '12 at 13:07
Please explain why the form needs to be created on the fly. Unless you have a specific need, as [Brad](http://stackoverflow.com/questions/11519345/creating-form-programmatically-in-the-module-using-vba-excel#comment15230104_11519345) explained it is a lot of work. — JimmyPena, Jul 17 '12 at 13:52
i want to make it dynamically just because i want to create an Addin so that another users can easily import that Addin in their Excel Sheet. ((If I create user form than they may also have to create a user form in order to use my Addin.)) — IConfused, Jul 18 '12 at 12:14

score 43 · Accepted Answer · answered Jul 18 '12 at 12:55

After hardwork I have found a very simple answer to my question. May help you also.

Sub CreateUserForm()
Dim myForm As Object
Dim NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
'Dim NewComboBox As MSForms.ComboBox
Dim NewListBox As MSForms.ListBox
'Dim NewTextBox As MSForms.TextBox
'Dim NewLabel As MSForms.Label
'Dim NewOptionButton As MSForms.OptionButton
'Dim NewCheckBox As MSForms.CheckBox
Dim X As Integer
Dim Line As Integer

'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)

'Create the User Form
With myForm
    .Properties("Caption") = "New Form"
    .Properties("Width") = 300
    .Properties("Height") = 270
End With

'Create ListBox
Set NewListBox = myForm.designer.Controls.Add("Forms.listbox.1")
With NewListBox
    .Name = "lst_1"
    .Top = 10
    .Left = 10
    .Width = 150
    .Height = 230
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BorderStyle = fmBorderStyleOpaque
    .SpecialEffect = fmSpecialEffectSunken
End With

'Create CommandButton Create
Set NewButton = myForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
    .Name = "cmd_1"
    .Caption = "clickMe"
    .Accelerator = "M"
    .Top = 10
    .Left = 200
    .Width = 66
    .Height = 20
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BackStyle = fmBackStyleOpaque
End With

'add code for listBox
lstBoxData = "Data 1,Data 2,Data 3,Data 4"
myForm.codemodule.insertlines 1, "Private Sub UserForm_Initialize()"
myForm.codemodule.insertlines 2, "   me.lst_1.addItem ""Data 1"" "
myForm.codemodule.insertlines 3, "   me.lst_1.addItem ""Data 2"" "
myForm.codemodule.insertlines 4, "   me.lst_1.addItem ""Data 3"" "
myForm.codemodule.insertlines 5, "End Sub"

'add code for Comand Button
myForm.codemodule.insertlines 6, "Private Sub cmd_1_Click()"
myForm.codemodule.insertlines 7, "   If me.lst_1.text <>"""" Then"
myForm.codemodule.insertlines 8, "      msgbox (""You selected item: "" & me.lst_1.text )"
myForm.codemodule.insertlines 9, "   End If"
myForm.codemodule.insertlines 10, "End Sub"
'Show the form
VBA.UserForms.Add(myForm.Name).Show

'Delete the form (Optional)
'ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub

NB, the command button needs an event-handler, so has to be hardcoded with `insertlines`. However anything in the `UserForm_Initialize` routine can actually come between `VBA.UserForms.Add(myForm.Name)` and `Show`. So for populating the list, `Set newUf = VBA.UserForms.Add(myForm.Name)`, then loop over a list of objects doing `newUf.lst_1.addItem` is much easier than hardcoding the code as strings. Finally `newUf.Show` once all set up — Greedo, Mar 22 '18 at 17:17

Creating form programmatically in the module using vba

1 Answers1

Linked