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.
Dear Sir!
I’m a Vietnamese. My English is so bad! So I have mistake in grammar, please forget about it!
In lesson http://www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/ i want to use combobox for copy-paste data to sheet2.
But I don’t know how to get value from combobox to transfer sheet2.
Please help me!
worksheets(“Sheet2”).Range(“A3”).value=Me.ComboBox1.Value
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.
This is exactly what I’m showing in the training video! Or, do you mean something else. Please elaborate.
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
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
Please explain how to add data to combo box through code
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