Create and add Controls dynamically in UserForm and Save data in SQL Server

In this tutorial, I’ll show you how to create and add ActiveX Controls dynamically in a UserForm and save all the data (extracted from the controls) in an SQL Server table.

create dynamic controls in userform and save data in sql server

Its easy to add controls in a userform in Excel. You can simply drag and drop controls from the Toolbox in the VBA editor into a UserForm in Excel. In-addition, you can create any ActiveX control dynamically and place the control in the UserForm at runtime, with the click of a button.

The userform in the below example, will have few dynamically created controls like "textbox", "label", "a combo box", "check box" and two "option boxes". Later we can use the dynamically created controls for data entry and save the data in an SQL Server database.

Create Tables in SQL Server

We’ll first create two tables in SQL Server. The tables are "dbo.Employee" and "dbo.Department".

The "Employee" table will store the employee data. The "Department" table will have a list of department names. These names will be populated in a Combo box in the UserForm.

Employee table:

CREATE TABLE [dbo].[Employee](
	[EmpID] [int] NOT NULL,
	[EmpName] [varchar](50) NOT NULL,
	[Designation] [varchar](50) NULL,
	[Department] [varchar](50) NULL,
	[JoiningDate] [datetime] NULL,
	[DateOfBirth] [datetime] NULL,
	[Gender] [varchar](10) NULL,
	[MaritalStatus] [varchar](10) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmpID] ASC )

Keep the employee table empty. We’ll fill the table from userform in Excel.

Department table:

CREATE TABLE [dbo].[Department](
	[ID] [int] NOT NULL,
	[Department_Name] [varchar](20) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ( [ID] ASC )

You can add few department names in the table, like Marketing, Sales etc.

Add a Module

We need to "set a connection" with our SQL Server database. The connections string etc. will be created and initialized in the Module section.

To insert a "Module", open the Project Explorer (in the VBA editor), right click the project, click Insert -> Module option.

add module in vba excel

To connect and communicate with the SQL Server database objects, I am using ADO connection object in VBA. So, we need to add a Reference of the ADO library in our 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).

➡️ If you don’t know what ADO is in VBA, then you must see this post. I have explained in detail about ADO (ActiveX Data Object) and how to use it to connect and pull data from SQL Server tables.

Add the below macro in the "Module" section.

Option Explicit

Public myConn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public sQuery As String

' Set the connection.
Sub setConn()
    If myConn.State = adStateOpen Then
        myConn.Close
    End If

    ' Define the connection string by provider driver and database details.
    Dim sConnString As String
    sConnString = "Driver={SQL Server};Server=server_name;Database=database_name;Uid=user_id;pwd=some_password;Connect Timeout=500;"

    myConn.ConnectionString = sConnString
    myConn.Open     ' Now, open the connection.
End Sub

Add UserForm in Excel

We’ll add a blank userform, since all controls will be created at runtime.

Follow these steps.

1) Open Excel and save the file in ".xlsm" format. In the VBA editor, open Project Explorer window, right the project and insert userform. See the image.

add userform in excel vba

Name the userform as "Employee Master".

2) Next, add a "Frame" control in the userform. This is the only control we’ll add at "design time". Click the Toolbox icon, drag and drop the frame control in the userform.

add frame in userform vba

The Frame will serve as a container. This is optional though.
Set the frame’s width as 400 and name it fraContainer.
Do not add any caption to the frame.

Add the below macro in the "UserForm".

Option Explicit

Public user_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 lblDOB As MSForms.Label          ' Date of Birth.
Public txtDOB As MSForms.TextBox

Public lblDOJ As MSForms.Label          ' Date of Joining.
Public txtDOJ As MSForms.TextBox

Public chkMarried As MSForms.CheckBox

Public fraGender As MSForms.Frame
Public optFemale As MSForms.OptionButton
Public optMale As MSForms.OptionButton

Public lblDepartment As MSForms.Label
Public cmbDepartment As MSForms.ComboBox

Public WithEvents btSaveData As MSForms.CommandButton

Private Sub UserForm_Initialize()
    fraContainer.Caption = ""
    fraContainer.BorderStyle = fmBorderStyleNone
    
    Call Init(UserForm1, fraContainer)          ' CREATE AND ADD CONTROLS.
End Sub

' Create controls like textbox, label etc. dynamically and place it on the UserForm.
Public Sub Init(myForm As UserForm1, fraMain As MSForms.Frame)
    Set user_form = myForm
    Set childFrame = fraMain.Controls.Add("Forms.Frame.1")
    
    With childFrame
        .Height = 210
        .Width = myForm.Width - 25
        
        With .Controls
        
            ' First header.
            Dim lblEmpInfoHeader As MSForms.Label
            Set lblEmpInfoHeader = .Add("Forms.Label.1")
            With lblEmpInfoHeader
                .Top = 0
                .Left = 0
                .Width = fraContainer.Width - 4
                .Height = 16
                .BackColor = RGB(61, 174, 73)
                .ForeColor = vbWhite
                .Font.Size = 11
                .TextAlign = fmTextAlignCenter
                .Caption = "Employee Information"
                .Name = "lblEmpInfoHeader"
            End With
        
            ' Employee Name.
            Set lblName = .Add("Forms.Label.1")
            With lblName
                .Top = 30
                .Left = 6
                .Caption = "Full Name"
                .Name = "lblName"
            End With
            
            Set txtName = .Add("Forms.TextBox.1")
            With txtName
                .Top = 28
                .Left = 90
                .Width = 130
                .Name = "fullName"
            End With
            
            ' Date of Birth.
            Set lblDOB = .Add("Forms.Label.1")
            With lblDOB
                .Top = 55
                .Left = 6
                .Caption = "Date of Birth"
                .Name = "lblDOB"
            End With
            
            Set txtDOB = .Add("Forms.TextBox.1")
            With txtDOB
                .Top = 52
                .Left = 90
                .Width = 130
                .Name = "dob"
            End With
            
            ' Add a check boxes. (Marital Status)
            Set chkMarried = .Add("Forms.CheckBox.1")
            With chkMarried
                .Top = 75
                .Left = 6
                .Width = 57
                .Height = 18
                .Caption = "Married"
                .Value = "married"
            End With
            
            ' *****
            ' Add a Frame for Option buttons.
            Set fraGender = fraMain.Controls.Add("Forms.Frame.1")
            With fraGender
                .Top = 25
                .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
            
            
            ' **** JOB INFORMATION.
            
            ' Second header.
            Dim lblJobInfoHeader As MSForms.Label
            Set lblJobInfoHeader = .Add("Forms.Label.1")
            With lblJobInfoHeader
                .Top = 100
                .Left = 0
                .Width = fraContainer.Width - 4
                .Height = 16
                .BackColor = RGB(61, 174, 73)
                .ForeColor = vbWhite
                .Font.Size = 11
                .TextAlign = fmTextAlignCenter
                .Caption = "Job Information"
                .Name = "lblJobInfoHeader"
            End With
            
            '  Department.
            Set lblDepartment = .Add("Forms.Label.1")
            With lblDepartment
                .Top = 125
                .Left = 6
                .Caption = "Department"
                .Name = "lblDepartment"
            End With
            
            ' Add combo box.
            Set cmbDepartment = .Add("Forms.ComboBox.1")
            With cmbDepartment
                .Top = 123
                .Left = 90
                .Width = 130
                .Height = 18
            End With
            
            Call fillCombo(cmbDepartment)            '  Fill the combo box with a list of department.
            
            ' Employee Designation.
            Set lblDesig = .Add("Forms.Label.1")
            With lblDesig
                .Top = 150
                .Left = 6
                .Caption = "Designation"
                .Name = "lblDesig"
            End With
            
            Set txtDesig = .Add("Forms.TextBox.1")
            With txtDesig
                .Top = 148
                .Left = 90
                .Width = 130
                .Name = "Designation"
            End With
            
            ' Date of Joining.
            Set lblDOJ = .Add("Forms.Label.1")
            With lblDOJ
                .Top = 175
                .Left = 6
                .Caption = "Date of Joining"
                .Name = "lblDOJ"
            End With
            
            Set txtDOJ = .Add("Forms.TextBox.1")
            With txtDOJ
                .Top = 173
                .Left = 90
                .Width = 130
                .Name = "doj"
            End With
          
            ' *****
            ' Finally, add the SAVE button.
            Set btSaveData = .Add("Forms.CommandButton.1")
            With btSaveData
                .Top = 145
                .Left = 260
                .Width = 100
                .Height = 25
                .Caption = "Save Data"
                .Font.Size = 10
            End With

        End With
    End With
    
    ' SET FONT SIZE OF LABEL ETC.
    Dim ctrl As Control      ' CREATE A CONTROL OBJECT.
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Or TypeName(ctrl) = "TextBox" _
            Or TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "OptionButton" _
            Or TypeName(ctrl) = "ComboBox" Then
            
            ctrl.Font.Size = 11
            ctrl.Font.Name = "corbel"
            
            If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                ctrl.BorderStyle = fmBorderStyleSingle
                ctrl.BorderColor = RGB(234, 168, 74)
            End If
        End If
    Next ctrl
End Sub

' Extract "department" list from SQL Server table and populate the combo box.
' The combo box is also created at run time.
Private Sub fillCombo(cmb As MSForms.ComboBox)
    setConn     ' Set connection.
    
    ' Get list of department from table and populate combo box.
    sQuery = "SELECT Department_Name from dbo.Department"
    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(0).Value      ' Fill the combo box.
            rs.MoveNext
        Loop
    Else
        MsgBox "The table is empty", vbCritical + vbOKOnly
        Exit Sub
    End If
End Sub

' Click event for Save button.
' The Save button is also created at run time.
Private Sub btSaveData_click()
    Call saveData(txtName.Text, txtDesig.Text, cmbDepartment.Value, txtDOJ.Value, txtDOB.Text, _
        IIf(optFemale, "Female", "Male"), IIf(chkMarried, "Married", "Un-Married"))
End Sub

' Procedure to save data extracted from the dynamically created controls.
Sub saveData(sEmpName As String, sDesig As String, sDept As String, sDOJ As String, sDOB As String, _
    sGender As String, sMaritalStatus As String)
    
On Error GoTo err_handler
        
    ' The Insert data query.
    Dim sInsertQuery As String
    sInsertQuery = "INSERT INTO dbo.Employee (EmpID, EmpName, Designation, Department, " & _
            "JoiningDate, DateOfBirth, Gender, MaritalStatus)  " & _
        "VALUES((SELECT MAX(EmpID) + 1 FROM dbo.Employee), '" & sEmpName & "', '" & sDesig & "', '" & sDept & "', " & _
        "CONVERT(date, '" & sDOJ & "', 104), CONVERT(date, '" & sDOB & "', 104), '" & _
        sGender & "', '" & sMaritalStatus & "')"
            
    ' Create a command object to save the data.
    Dim objCom As New ADODB.Command
    With objCom
        .CommandText = sInsertQuery
        .ActiveConnection = myConn
        .CommandType = adCmdText
        .Execute
    End With
    
    MsgBox ("Employee created.")
    
    ' Clear all the data in the fields.
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        ' 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
    
err_handler:
    '
End Sub

I agree, it’s a big code 🙏. Since we are creating the controls dynamically in the userform, we’ll have to add properties and events of each control, dynamically.

Most importantly, we’ll have to place the controls, "where it should be", by assigning values to "Top" and "Left" properties of each control. At design time, you can simply drag and drop the controls where you want it to be.

The form also has an ActiveX button control (to save data) and a combo box (a dropdown list).

Since the button will perform an action, it must have "events attached to it". Therefore, we’ll create a button control with events. Check this line in the beginning of the code... Public WithEvents btSaveData As MSForms.CommandButton.

The combo box will have a list of department names, extracted from dbo.Department table in SQL Server, which we have created in the beginning of this tutorial.

Finally, data is saved in "dbo.Employee" table.

😀 If you have any doubt regarding the code in this tutorial, drop me an email.

🚀 If you want me to design a complete employee form in Excel using UserForm and VBA, at a nominal price, drop me an email.

← PreviousNext →