Welcome to Excel Avon
Data Entry with Option Button
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post we will tell you Data Entry with option button by User Form in Excel VBA, in previous post we taught you some functions to introduce and use Option button in Userform in Excel VBA, VBA has the ability to create forms that users can interact with such as invoice forms or contact details. A form can contain many types of form controls such as text boxes, combo boxes, list boxes, and command buttons.
This article will cover the most popular controls used on user forms, such as option buttons.
Data Entry with Option Button by UserForm
Ok, you should know how to insert UserForm, let’s understand. For how to use Data Entry with Option Button by UserForm, we have to go to the Developer Tab like last time and then click on the option of Visual Basic as shown in the picture 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.
As you can see now, we have inserted UserForm, we will do some more work like insert label, text box and Option button. I will select Lebel 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 (txtName) 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 go to Toolbox and select frame and drag frame in UserForm. After this, change the name of the Frame by going to properties.
Now we will Insert option Button in frame. Here we will insert two option buttons which are for gender Male and Female.
Now we will change the name and caption of both option button.
By going to the properties of the option button, change the name and caption to male and female.
Now we will insert another frame which will be used for standard frame and then insert some option buttons in that too. After inserting the frame we will go to properties then we will change the frame caption.
After changing the caption, we will go back to the toolbox, then insert more option buttons, and also change the caption and name of the button.
Similarly, we will insert the option button in the frame as many options as we have to make and change the caption of all, for which the button has to be selected, then go to the property and change the caption and name.
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 CommandButton1_Click() Dim Ws As Worksheet Dim lr As Long Dim Gender As String Dim Standard As String End Sub
Set worksheet as Activesheet, here we’ll Declare Last row.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
Set Ws = ActiveSheet
lr = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1
End Sub
Now we will create the Range to fill the name in the Worksheet.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
Set Ws = ActiveSheet
lr = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1
Ws.Range("A" & lr).Value = Me.txtName.Value
End Sub
Now we will again create another range to fill the date of birth in the worksheet.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
End Sub
Now we will create the condition, if male option is selected in Userform then gender cell will be filled with male.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
End If
End Sub
Now we will create one more condition, which will be for female option, if female option is selected in UserForm then gender cell will be filled with female.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
ElseIf Me.optFemale.Value = True Then
Gender = "Female"
End If
End Sub
Now we will create condition for option button of standard frame. If 1st standard option is selected in option button, then standard cell will be filled with 1st.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
ElseIf Me.optFemale.Value = True Then
Gender = "Female"
End If
If Me.opt1.Value = True Then
Standard = "1st"
End If
End Sub
The way the code is written for the 1st standard, in the same way it will be written for all the standards, you can also copy and paste, just you will have to change the value.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
ElseIf Me.optFemale.Value = True Then
Gender = "Female"
End If
If Me.opt1.Value = True Then
Standard = "1st"
ElseIf Me.opt2.Value = True Then
Standard = "2nd"
ElseIf Me.opt3.Value = True Then
Standard = "3rd"
ElseIf Me.opt4.Value = True Then
Standard = "4th"
ElseIf Me.opt5.Value = True Then
Standard = "5th"
End If
End Sub
Now we will declare the gender and standard worksheet range.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
ElseIf Me.optFemale.Value = True Then
Gender = "Female"
End If
If Me.opt1.Value = True Then
Standard = "1st"
ElseIf Me.opt2.Value = True Then
Standard = "2nd"
ElseIf Me.opt3.Value = True Then
Standard = "3rd"
ElseIf Me.opt4.Value = True Then
Standard = "4th"
ElseIf Me.opt5.Value = True Then
Standard = "5th"
End If
Ws.Range("C" & lr).Value = Gender
Ws.Range("D" & lr).Value = Standard
End Sub
Now we will use the message box ‘Data Submitted’ with VbInformation sign.
Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Dim lr As Long
Dim Gender As String
Dim Standard As String
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.txtDOB.Value
If Me.optMale.Value = True Then
Gender = "Male"
ElseIf Me.optFemale.Value = True Then
Gender = "Female"
End If
If Me.opt1.Value = True Then
Standard = "1st"
ElseIf Me.opt2.Value = True Then
Standard = "2nd"
ElseIf Me.opt3.Value = True Then
Standard = "3rd"
ElseIf Me.opt4.Value = True Then
Standard = "4th"
ElseIf Me.opt5.Value = True Then
Standard = "5th"
End If
Ws.Range("C" & lr).Value = Gender
Ws.Range("D" & lr).Value = Standard
MsgBox "Data submitted!", vbInformation
End Sub
We haven’t yet created the macro that will show the UserForm First go to the Insert tab then Insert Module. Data Entry with Option Buttons by Userform with Excel VBA.
Once the module is inserted, and we’ll write a subroutine to Sub show Data Form. Macro has been created which will only show the data form in the sheet.
Sub ShowForm() UserForm1.Show End Sub
Open the worksheet and go to insert tab drag a button for assign macro
First, name the button and then, we’ll assign Macro. Select macro and click Ok.
Now click on the Show Form button. The 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.
Therefore, I hope that you have understood Data Entry with Option Button 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 Option Button by UserForm in Excel VBA
