Welcome to Excel Avon
Data Entry with ComboBox
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post we will tell you Data Entry with ComboBox by UserForm in Excel VBA, in previous post we taught you How to use ComboBox in UserForm in Excel VBA, Combobox is a UserForm control in VBA. And we already know how to use ComboBox, they are different from textbox because textbox contains only text. We allow the user to input any data, but by using a ComboBox, we limit the type of response the user wants. Thus, the data is in an orderly fashion. This is similar to list data validation in Excel.
We will create a data column for data entry then fill it with data and then we will add text box to it and use Combobox with it. ComboBox is similar to the dropdown list we have in excel worksheet. With combo box.
Data Entry with ComboBox by UserForm
Ok, you must know how to insert UserForm, let’s understand. For how to use ComboBox in UserForm, we have to go like last time first go to Developer Tab then click on Visual Basic’s Option as shown in image below.
On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.
We have inserted the Userform as you can see now, we will do some further work like insert label, text box or ComboBox.
We will go to the UserForm1 properties, Change the UserForm Caption with UserForm Name (Dataform).
Now I will select Lebal in toolbox, drag in UserForm, then we will change the name of the label by going to the properties of the label.
Now we will go to the toolbox and insert a text box in the UserForm. We will again change the name of the textbox by going to the property.
Drag the label, we will insert as many labels as there are properties. After inserting the label in UserForm we will name the label as. We will insert the text box with the label.
Now we will insert the label and into the UserForm. After this, change the name of the label by going to properties.
For label class and gender, we will insert combobox and then change the name of combobox, like txtgender, txtclass.
Now we will add two command buttons in UserForm. with this we Rename Command Button ‘Submit‘ and ‘Close‘.
Double click on close button, Double clicks on close button then we will create code for close button unload.me will use.
Private Sub CmdClose_Click() Unload Me End Sub
Double click on Submit button. and Define Variables.
Private Sub cmdSubmit_Click() Dim WS As Worksheet Dim lr As Long End Sub
Set worksheet as Activesheet, here we’ll Declare Last row.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
End Sub
Now we will create the range to fill the names in the worksheet.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
End Sub
Copy and paste Text Boxes code. and change cell.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtName.Value
WS.Range("C" & lr).Value = Me.txtName.Value
WS.Range("D" & lr).Value = Me.txtName.Value
WS.Range("E" & lr).Value = Me.txtName.Value
End Sub
And then Change Text Boxes Name.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtAddress.Value
WS.Range("C" & lr).Value = Me.txtPhone.Value
WS.Range("D" & lr).Value = Me.cmbGender.Value
WS.Range("E" & lr).Value = Me.cmbClass.Value
End Sub
We will write the code to clear the textbox once the value is filled to worksheet. copy and paste that code and change text boxes names.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtAddress.Value
WS.Range("C" & lr).Value = Me.txtPhone.Value
WS.Range("D" & lr).Value = Me.cmbGender.Value
WS.Range("E" & lr).Value = Me.cmbClass.Value
Me.txtName.Value = ""
Me.txtAddress.Value = ""
Me.txtPhone.Value = ""
Me.cmbGender.Value = ""
Me.cmbClass.Value = ""
End Sub
Now we will use the message box ‘Data Submit Successfully’ with VbInformation sign.
Private Sub cmdSubmit_Click()
Dim WS As Worksheet
Dim lr As Long
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtAddress.Value
WS.Range("C" & lr).Value = Me.txtPhone.Value
WS.Range("D" & lr).Value = Me.cmbGender.Value
WS.Range("E" & lr).Value = Me.cmbClass.Value
Me.txtName.Value = ""
Me.txtAddress.Value = ""
Me.txtPhone.Value = ""
Me.cmbGender.Value = ""
Me.cmbClass.Value = ""
MsgBox "Data submitted successfully!", vbInformation
End Sub
Now we will Go back to UserForm, Right click on Gender ComboBox click view code.
Now we will open the drop-down list and we will select initialize.
Remove private Subroutine. Here an initialize subroutine will be created. Now add item to UserForm ComboBox manually for Male and Female.
Private Sub UserForm_Initialize() Me.cmbGender.AddItem "Male" Me.cmbGender.AddItem "Female" End Sub
Now we will go to the UserForm, we will right click on the class ComboBox.
Now we will go to the worksheet, then now we will select the range of the value which is written and give a new name to that range(rngClass).
Copy the name of the same range and then go to the properties of the Class combo box in VBE and paste the name of the range in the row source with eqaul(=RangeName).
Go to the Activesheet. And Click on Insert Icon Select a button. Drag on Worksheet .
We have not yet created the macro which will show the Dataform then now we will go to VBE to create the macro then firstly go to the insert tab then insert the module. Data Entry with ComboBox by UserForm with Excel VBA.
Once the module is inserted, and we’ll write a subroutine to Sub Open Data Form. Macro has been created which will only show the data form in the sheet.
Sub OpenDataForm()
DataForm.Show
End Sub
Now open worksheet. and we’ll assign Macro. Select macro and click Ok.
Now click on the Add Data button. The data form has been opened, now we will fill the information in it.
First text box is for Name, so we’ll write the name. Similarly fill all the data. and click submit button.
We will get the message of ‘Data submitted Successfully’.
Now Close UserForm.
Now you can see data is filled in Worksheet.
Therefore, I hope that you have understood Data Entry with ComboBox by UserForm in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about Data Entry with ComboBox by UserForm in Excel VBA
