Excel VBA - Dynamically create controls in UserForm and save data in Ms-Access

Last updated: 27th June 2025

Learn how to add and manage controls in an Excel UserForm with ease. You can simply drag and drop multiple controls from the toolbox directly onto the UserForm. But did you know you can also create UserForm controls dynamically at runtime using VBA? In this tutorial, I’ll demonstrate how to generate controls programmatically in Excel and efficiently store the input data into a Microsoft Access table. Perfect for automating forms and enhancing your Excel-VBA applications.

👉 This example doesn't include every ActiveX control available in the toolbox. However, it dynamically creates several essential controls, such as TextBox, ComboBox, and CheckBox and adds them to a Frame within the UserForm. This approach demonstrates how to programmatically add key controls using VBA, streamlining your Excel form development

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 ActiveX Command Button with Events, at run time, so you can save the data.

Step 1: 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.

Create a Table in Ms-Access

Step 2: 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.

Add a UserForm in Excel VBA

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.

Step 3: Add a Module to Create ActiveX Data Objects

In this example, the UserForm includes a ComboBox (dropdown list) populated with a list of countries. Rather than hardcoding these values, given that the list may be extensive, I’ve chosen to store the country names in a separate worksheet (Sheet2), which I’ve renamed to "Country." The data is then dynamically extracted from this sheet and loaded into the ComboBox at runtime.

Using Excel as a Database

To accomplish this, I use an ADO (ActiveX Data Objects) connection to query the worksheet as if it were a database table. This method ensures that the ComboBox always reflects the most up-to-date list of countries without requiring code changes.

In the VBA editor, go to the Tools menu and select References. In the References dialog box, scroll through the list and check the box for Microsoft ActiveX Data Objects 6.1 Library (or a compatible version available on your system), then click OK to confirm.

Now, add this code in the 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

' Set a connection.
Sub SetConn(sConnString As String)

    If myConn.State = adStateOpen Then
        myConn.Close
    End If

    With myConn
        .ConnectionString = sConnString
        .Open         ' Open the connection.
    End With
End Sub

The Code (Macro)

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
        
            ' Employee Name.
            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
            
            ' Employee Designation.
            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
            
            ' *****
                ' Add a line.
            Set lblLine = .Add(bstrProgID:="Forms.Label.1")
            With lblLine
                .Top = 45
                .Caption = "______________________________________"
                .Width = myForm.Width
                .ForeColor = vbBlue
                .TextAlign = fmTextAlignCenter
            End With
            
            
            ' *****
                ' Add two check boxes.
            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
            
            ' *****
                ' Add a Frame for Option buttons.
            Set fraGender = fraMain.Controls.Add("Forms.Frame.1")
            With fraGender
                .Top = 2
                .Left = 250
                .Height = 45
                .Width = 135
                .Caption = "Gender"
                
                With .Controls
                    ' Add two option boxes.
                    
                    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
            
            ' *****
                ' Country.
            Set lblCountry = .Add("Forms.Label.1")
            With lblCountry
                .Top = 90
                .Left = 6
                .Caption = "Country"
                .Name = "lblCountry"
            End With
            
            ' Add combo box.
            Set cmbCountry = .Add("Forms.ComboBox.1")
            With cmbCountry
                .Top = 90
                .Left = 90
                .Width = 130
                .Height = 18
            End With
            
            ' Fill the combo box with a list of countries dynamically.
            Call fillCombo(cmbCountry)
           
            ' *****
                ' Finally, add the submit button.
            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

' Click event for the dynamically created Submit button.
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)
    ' Fill combo box with data extracted from Sheet2 (or sheet name Country).
    ' Reference: https://www.encodedna.com/excel/how-to-create-cascading-combo-box-in-excel-using-vba.htm

    sConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    SetConn(sConnString)    ' Set connection.
   
    sQuery = "SELECT *from [Country$]"          ' Read data in sheet name 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

' Procedure to save data extracted from the dynamically created fields.
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)
    
    ' The Insert data query.
    Dim sInsertQuery As String
    sInsertQuery = "INSERT INTO Employee (EmployeeName, Designation, Gender, Misc1, Misc2, Country)  " & _
        " VALUES('" & sEmpName & "','" & sDesig & "', '" & sGender & "', '" & _
            sMisc1 & "', '" & sMisc2 & "', '" & sCountry & "')"
            
    ' Save the data.
    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
        ' Clear all the data in the fields.
        ' Reference: https://www.encodedna.com/excel/loop-through-all-textboxes-in-userform-and-clear-the-values-in-vba.htm 
        
        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

I've 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.

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 (I have created in the beginning).

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.

Dynamically create controls in Excel UserForms and Save data in a DataBase

← PreviousNext →