Adding Combobox to User Form

In the same earlier userform we add a combo box control for designations. We also add code to transfer the data from the combo-box to the Excel worksheet where we have also inserted the header ‘designation’ between the name and salary headers.
The complete revised code is given below with the changes highlighted using bold or italic fonts..

Private Sub cmdAddData_Click()
Dim RowCount As Long
Dim benefits, total As Single
If Me.txtName.Value = “” Then
MsgBox “Please enter a name”, vbExclamation, “Employee Data”
Me.txtName.SetFocus
End If
‘Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
Me.txtSalary.SetFocus
End If
‘Range(“B5”) = txtSalary.Value
benefits = txtSalary.Value * 0.5
‘Range(“C5”) = benefits
total = txtSalary.Value + benefits
‘Range(“D5”) = total
RowCount = Worksheets(“Sheet1”).Range(“A4”).CurrentRegion.Rows.Count
With Worksheets(“Sheet1”).Range(“A4”)
.Offset(RowCount, 0) = Me.txtName.Value
.Offset(RowCount, 1) = Me.ComboBox1.Value
.Offset(RowCount, 2) = Me.txtSalary.Value
.Offset(RowCount, 3) = benefits
.Offset(RowCount, 4) = total
End With
End Sub

In sheet2 we create a list of designations, select the data and assign a name to the range in the name box. Named ranges have been discussed in greater detail earlier.
In our earlier form we insert a label for designation and a combobox. In the ‘properties’ of the combobox under ‘rowsource’ we write our named range ‘designations’. Here you could also assign the name ‘cboDesignation’ for the combobox.
We then double-click on the command button and modify the code appropriately as shown above to take care of our designations.

We’ll continue to further modify this user-form to make it more user-friendly.

Let’s watch the video below to understand the complete process.