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.


7 thoughts on “Adding Combobox to User Form

  1. Rajarshi Chakraborty

    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.

    Reply
  2. Hiltonel

    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

    Hiltonel

    Reply
  3. Zafar Janjua

    hi
    sir,
    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

    Regards
    Zafar Janjua

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *