How to Create an user form via MS VBA in Excel to collect data of questionnaire efficiently?

Purpose: Create an user form to allow banking users from different areas and branches to enter their target customers and offering products. The feedback will be sent to a worksheet in the same Excel Sheet.

1. Define columns in the tab “Database”.

2. Under “Developer” section, go to “View Code”. Insert an user form named “UserForm1”.

3. Under “View”, open “Properties Window”.

4. In the Tool Box, add “MultiPage”, “Label”, “TextBox” and “CommandButton”.

5. Define the variable name for each component.

6. In the second page “Customer”, create three ComboBoxes, named “TargetCustomerCB”, “AgeCB” and “OccupationCB”, which control single selections for the fields of “Target Customers”, “Age Range”, and “Occupation” respectively. There are two ways to manage the values in the combo box. Click “View Code” on the UserForm1 and select “Initialize” then it will create a Private Sub named “UserForm_Initialize()”.

6.1 We can add time by using .AddItem “Item”.

With Me.TargetCustomerCB
    .AddItem "New"
    .AddItem "Existing"
End With

6.2 We can also create Range named “Age” in the worksheet “LookupLists”. In the worksheet “LookupLists”, under “Formulas” section, click on “Name Manager”, which can help us define the Range. To manage the values flexibly, use Offset in the “Refer to” text box.

=OFFSET(LookupLists!$A$2,0,0,COUNTA(LookupLists!$A:$A),1)

When the Name is created, add value via .AddItem.

For Each cAge In ws.Range("Age")
  With Me.AgeCB
    .AddItem cAge.Value
  End With
Next cAge

The full code under “UserForm_Initialize()”

Private Sub UserForm_Initialize()
Dim cAge As Range
Dim cOccupation As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")  With Me.TargetCustomerCB
    .AddItem "New"
    .AddItem "Existing"
  End With
  
For Each cAge In ws.Range("Age")
  With Me.AgeCB
    .AddItem cAge.Value
  End With
Next cAgeFor Each cOccupation In ws.Range("Occupation")
  With Me.OccupationCB
    .AddItem cOccupation.Value
  End With
Next cOccupationEnd Sub

7. In the third page “Product”, we created four Check Boxes for the four products named by “Product1_1CB”, “Product1_2CB”, Product1_3CB and Product1_4CB.

8. Add a Command Button to send the result back to the worksheet “Database”.

Private Sub CommandButton1_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1With ws
  .Cells(lRow, 1).Value = Me.NameTB.Value
  .Cells(lRow, 2).Value = Me.BranchTB.Value
  .Cells(lRow, 3).Value = Me.BranchLeaderTB.Value
  .Cells(lRow, 4).Value = Me.ManagerTB.Value
  .Cells(lRow, 5).Value = Me.TargetCustomerCB.Value
  .Cells(lRow, 6).Value = Me.AgeCB.Value
  .Cells(lRow, 7).Value = Me.OccupationCB.Value  If Product1_1CB.Value = True Then
  .Cells(lRow, 8).Value = "Y"
  Else
  .Cells(lRow, 8).Value = "N"
  End If
  
  If Product1_2CB.Value = True Then
  .Cells(lRow, 9).Value = "Y"
  Else
  .Cells(lRow, 9).Value = "N"
  End If
  
  If Product1_3CB.Value = True Then
  .Cells(lRow, 10).Value = "Y"
  Else
  .Cells(lRow, 10).Value = "N"
  End If
  
  If Product1_4CB.Value = True Then
  .Cells(lRow, 11).Value = "Y"
  Else
  .Cells(lRow, 11).Value = "N"
  End If
End WithEnd Sub

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲