learn microsoft excel

Adding and Using a Combobox on a UserForm

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training
Blog
Interactive Training

In the same earlier userform we add a combobox control for designations. We also add code to transfer the data from the combobox 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 greate 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.


Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Interactive Training