You can easily add multiple controls to a UserForm in Excel, simply by dragging and dropping the controls from the toolbox on to the form. Do you know you can create the UserForm controls at runtime? Yes, you can and here I am sharing an example that shows how to create controls dynamically in a UserForm using VBA and save all the data to an Ms-Access table.

The example here does not cover or create all the that are available in the toolbox, but it creates some of the most important controls (like the textbox, combo, checkbox etc.) dynamically and adds it to a Frame inside the UserForm.

Related Post:

It’s a simple data entry form, where a user can enter details about an Employee (its like an Employee master) and save the data in a database (Ms-Access).

The most important part of this example is that it creates an with Events, at run time, so you can save the data.

Create a Table in Ms-Access

Since, I’ll be saving the data in the UserForm in an Ms-Access table, I’ll first create the table. Open Ms-Access, and from the top menu, find Create and choose Table option. Name the table as Employee and add few fields to the table. See the image.

Add a UserForm in Excel

Open an Excel file and save the file as Macro or in .xlsm format. To add a UserForm, you will have to open the VBA editor. You can open the editor by right clicking the Sheet1 tab (or any tab at the bottom of your worksheet) and choose View Code option or simply press Alt+F11 keys.

Next, open Project Explorer window, right click the project to Insert a UserForm.

From the ToolBox, drag and drop a Frame on to the UserForm. This is the only control we’ll actually add at design time. The Frame will serve as the main or parent container for all the controls that we’ll create at run time. Don’t add any caption to the frame.

Add a Module to Create ActiveX Data Objects

The UserForm in this example, has a , which has a list of Countries. Since this list will have many values, I don’t want to hardcode the values. Instead, I’ll add the list of countries in Sheet2 in my worksheet (I have named Sheet2 as Country), and extract and fill this data to the ComboBox, at run time.

I am using ADO connection object to set a connection and fetch records from Sheet2 (for the combo box). Therefore, you must add the ADO library reference in your application.

From the top menu in your VBA editor, click Tools and choose References. In the Reference window, find and select Microsoft ActiveX Data Objects 6.1 Library (or any other version).

Now, add this code in your Module.

Option Explicit Public myConn As New ADODB .Connection Public myComm As New ADODB.Command Public rs As New ADODB.Recordset Public sQuery As String Sub SetConn(sConnString As String) If myConn.State = adStateOpen Then myConn.Close End If With myConn .ConnectionString = sConnString . Open End With End Sub

The Code to create and add UserForm Controls dynamically

Option Explicit Public Form As UserForm1 Public childFrame As MSForms.Frame Public txtName As MSForms.TextBox Public lblName As MSForms.Label Public txtDesig As MSForms.TextBox Public lblDesig As MSForms.Label Public chkMisc1 As MSForms.CheckBox Public chkMisc2 As MSForms.CheckBox Public fraGender As MSForms.Frame Public optFemale As MSForms.OptionButton Public optMale As MSForms.OptionButton Public lblLine As MSForms.Label Public lblCountry As MSForms.Label Public cmbCountry As MSForms.ComboBox Public WithEvents btSubmit As MSForms.CommandButton Dim sConnString As String Private Sub UserForm_Initialize () fraContainer.Caption = "" fraContainer.BorderStyle = fmBorderStyleNone Call Init (UserForm1, fraContainer) End Sub Public Sub Init (myForm As UserForm1, fraMain As MSForms.Frame) Set Form = myForm Set childFrame = fraMain.Controls. Add ("Forms.Frame.1") With childFrame .Height = 155 .Width = myForm.Width - 25 With .Controls Set lblName = .Add("Forms.Label.1") With lblName .Top = 10 .Left = 6 .Caption = "Employee Name" .Name = "lblName" End With Set txtName = .Add("Forms.TextBox.1") With txtName .Top = 8 .Left = 90 .Width = 130 .Name = "EmpName" End With Set lblDesig = .Add("Forms.Label.1") With lblDesig .Top = 30 .Left = 6 .Caption = "Designation" .Name = "lblDesig" End With Set txtDesig = .Add("Forms.TextBox.1") With txtDesig .Top = 28 .Left = 90 .Width = 130 .Name = "Designation" End With Set lblLine = .Add(bstrProgID:="Forms.Label.1") With lblLine .Top = 45 .Caption = "______________________________________" .Width = myForm.Width .ForeColor = vbBlue .TextAlign = fmTextAlignCenter End With Set chkMisc1 = .Add("Forms.CheckBox.1") With chkMisc1 .Top = 60 .Left = 6 .Width = 57 .Height = 18 .Caption = "Misc 1" .Value = "Msc 1" End With Set chkMisc2 = .Add("Forms.CheckBox.1") With chkMisc2 .Top = 60 .Left = 90 .Width = 57 .Height = 18 .Caption = "Misc 2" .Value = "Msc 2" End With Set fraGender = fraMain.Controls.Add("Forms.Frame.1") With fraGender .Top = 2 .Left = 250 .Height = 45 .Width = 135 .Caption = "Gender" With .Controls Set optFemale = .Add("Forms.OptionButton.1") With optFemale .Top = 10 .Left = 7 .Width = 57 .Height = 18 .Caption = "Female" End With Set optMale = .Add("Forms.OptionButton.1") With optMale .Top = 10 .Left = 70 .Width = 57 .Height = 18 .Caption = "Male" End With End With End With Set lblCountry = .Add("Forms.Label.1") With lblCountry .Top = 90 .Left = 6 .Caption = "Country" .Name = "lblCountry" End With Set cmbCountry = .Add("Forms.ComboBox.1") With cmbCountry .Top = 90 .Left = 90 .Width = 130 .Height = 18 End With Call fillCombo (cmbCountry) Set btSubmit = .Add("Forms.CommandButton.1") With btSubmit .Top = 120 .Left = 90 .Width = 130 .Height = 25 .Caption = "Submit" End With End With End With End Sub Private Sub btSubmit_click () Call SaveData (txtName.Text, txtDesig.Text, IIf(optFemale, "Female", "Male"), _ IIf(chkMisc1 <> "", chkMisc1.Value, ""), IIf(chkMisc2 <> "", chkMisc2.Value, ""), cmbCountry.Value) End Sub Private Sub fillCombo (cmb As MSForms.ComboBox) sConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _ "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name SetConn(sConnString) sQuery = "SELECT *from [Country$]" cmb.Clear If rs.State = adStateOpen Then rs.Close End If rs.CursorLocation = adUseClient rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF cmb.AddItem rs.Fields(1).Value ' Fill the combo box. rs.MoveNext Loop Else MsgBox "There are no categories in the list.", vbCritical + vbOKOnly Exit Sub End If End Sub Sub SaveData (sEmpName As String, sDesig As String, sGender As String, sMisc1 As String, _ sMisc2 As String, sCountry As String) sConnString = "PROVIDER = Microsoft.ACE.OLEDB.12.0; Data Source = E:\Employee.accdb; Persist Security Info=False" SetConn (sConnString) Dim sInsertQuery As String sInsertQuery = "INSERT INTO Employee (EmployeeName, Designation, Gender, Misc1, Misc2, Country) " & _ " VALUES('" & sEmpName & "','" & sDesig & "', '" & sGender & "', '" & _ sMisc1 & "', '" & sMisc2 & "', '" & sCountry & "')" With myComm .CommandText = sInsertQuery .ActiveConnection = myConn .CommandType = adCmdText .Execute End With myConn.Close MsgBox ("New employee data created!") Dim ctrl As Control For Each ctrl In Me.Controls If TypeName(ctrl) = "TextBox" Or _ TypeName(ctrl) = "CheckBox" Or _ TypeName(ctrl) = "ComboBox" Or _ TypeName(ctrl) = "OptionButton" Then ctrl.Value = "" End If Next ctrl End Sub

The code looks a bit lengthy, but its useful. I’ll explain it.



I have declared all the control objects in the beginning. The frame named childFrame is attached to the main frame or the parent frame (which we have added in the beginning). The childFrame will actually have all the other controls that I am creating at run time.

Also Read:

I have set the width and height of the childFrame, so it can accomodate all the controls.

With childFrame .Height = 155 .Width = myForm.Width – 25 With .Controls … End With

Next, I'll add the remaining controls, like the textboxes, labels, option buttons, checkboxes etc. to the childFrame. Each control is placed on the frame using its .Top and .Left properties. This might look like time consuming, but its worth it. Even, dragging and dropping the controls at a specific location on the form, takes time.

I also have a Submit button. I have created a CommanButton object in the beginning using the WithEvents property. Buttons, like any other controls, have events. I need to use the button’s Click event to submit the data.

Creating and using the events at run time is simple.

Public WithEvents btSubmit As MSForms.CommandButton

Here's how you declare the Event.

Private Sub btSubmit_click () Call SaveData(txtName.Text, txtDesig.Text, IIf(optFemale, "Female", "Male"), _ IIf(chkMisc1 <> "", chkMisc1.Value, ""), IIf(chkMisc2 <> "", chkMisc2.Value, ""), cmbCountry.Value) End Sub

Along with this, I have two more procedures.



• Procedure fillCombo() fills a ComboBox with a list of Countries. See how I am using an SQL like query SELECT *from [Country$] to fetch the data from my Excel worksheet. The procedure takes a parameter in the form of an object.

Private Sub fillCombo(cmb As MSForms.ComboBox) End Sub

• Procedure SaveData() saves the data in the UserForm to an Ms-Access table named Employee ( ).

The procedure takes few string parameters. These are the data fetched from the dynamically created controls in the UserForm.

Here again I am creating an ADO connection. Look at the connection string and the Provider I have used to connect to an Ms-Access database.

That’s it. Run the application. If everything goes right, you should have a simple, basic form, with some dynamically created controls. Fill the form with some values and click the Submit button.

Thanks for reading. ☺

← PreviousNext →