March 1, 2014

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”
End If
‘Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
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.

11 thoughts on “Adding Combobox to User Form

  1. I have a simple user form in excel2007, now I want to add Combo Box in few of the fields. Please let me know how to do it? I can send you the excel file so that you can have a better idea. Thanks for your time.

    1. This is exactly what I’m showing in the training video! Or, do you mean something else. Please elaborate.

  2. Dear sir,
    My tool menue does not have the microsoft monthview control. I need that to set time and date in my userform. I am using excel 2013.

    I will deeply appreciate your help.

    Thanks in adavance


  3. hi
    i have problem in excelvb need help excel sheet1 have four columns one column ligical mean row is empty row=0 and active row=1 with some or emty row i need to display in list box only active row empty row=0 not display

    waiting for positive response

    Zafar Janjua

  4. dear sir,

    i am creating two combo boxes
    in combo box 1 each case the other combo box 2 link to some list and each combo box 2 list some data should show in text boxes like that i wank make user form please provide me

  5. hello

    I already copy and paste your code but i cant run the system. could you show me the property of every text box

Comments are closed.