Data Entry with List box by UserForm in Excel VBA Easily (3 Example)

Welcome to Excel Avon

Data Entry with List Box

DOWNLOAD USED EXCEL FILE FROM HERE>>

In today’s post we will tell you Data Entry with List box by User form in Excel VBA with List box, in previous post we taught you some functions to introduce and use List box in UserForm in Excel VBA, List box in Excel VBA is a list to which a variable is assigned. This list has various inputs to select from and allows multiple options to be selected at once. A list box can be inserted on a User Form by selecting the List Box option.

List boxes use named ranges of a certain number of values. The purpose of using a list box is to display a list of values that the user can select from.

Data Entry with List Box in UserForm

List Box is one of the tools under user form in VBA. As the name itself suggests list box will hold all the list of values given by the user. In VBA List box comes with a user form. In this article, we will see how to create a list box and how to hold values in the list box in Excel VBA.

Ok, you should know how to insert UserForm, let’s understand. For how to use List box in 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 list box.

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 (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 insert the label and into the UserForm. After this, change the name of the label by going to properties.

For label subject, we will insert ListBox and then change the name of lstSubject.

Now we will go to the worksheet and then after selecting the data of our list box, we will give a name to that range and copy that name.

Copy the name of the same range and then go to the properties of the List Box in VBE and paste the name of the range in the row source with equal(=RangeName).

The data has come in the list box as soon as pasted.

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 lr As Long, WS As Worksheet
Dim subStr As String, i As Long
End Sub

Set worksheet as Activesheet, here we’ll Declare Last row.

Private Sub cmdSubmit_Click() Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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 name in the Worksheet.

Private Sub cmdSubmit_Click() Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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 lr As Long, WS As Worksheet
Dim subStr As String, i 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
End Sub

And then Change Text Boxes Name.

Private Sub cmdSubmit_Click() Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
End Sub

Clear Sub string and Make a Loop with ListSubject and List Count.

Private Sub cmdSubmit_Click() Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
Next i
End Sub

Now we will create a condition, if the item is selected from the range of the UserForm that we have created where the data is there, then we are using the second condition next to it.

Private Sub CommandButton1_Click()
Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
    If Me.lstSubject.Selected(i) = True Then
    End If
Next i
End Sub

The next condition is something like ‘then if the substring is empty then that selected item should come in the substring’

Private Sub CommandButton1_Click()
Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
    If Me.lstSubject.Selected(i) = True Then
       If subStr = "" Then
          subStr = Me.lstSubject.List(i)
       End If
    End If
Next i
End Sub

Now we will create another condition which if we select more than one subject then it will be for that.

Private Sub CommandButton1_Click()
Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
    If Me.lstSubject.Selected(i) = True Then
       If subStr = "" Then
          subStr = Me.lstSubject.List(i)
       Else 
          subStr = subStr & ", " & Me.lstSubject.List(i)
       End If
    End If
Next i
End Sub

For sub string we will define worksheet range.

Private Sub CommandButton1_Click()
Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
    If Me.lstSubject.Selected(i) = True Then
       If subStr = "" Then
          subStr = Me.lstSubject.List(i)
       Else 
          subStr = subStr & ", " & Me.lstSubject.List(i)
       End If
    End If
Next i
WS.Range("D" & lr).Value = subStr
End Sub

Now we will use the message box ‘Data Submit Successfully’ with VbInformation sign.

Private Sub CommandButton1_Click()
Dim lr As Long, WS As Worksheet
Dim subStr As String, i 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.txtPhone.Value
WS.Range("C" & lr).Value = Me.txtID.Value
subStr = ""
For i = 0 To Me.lstSubject.ListCount - 1
    If Me.lstSubject.Selected(i) = True Then
       If subStr = "" Then
          subStr = Me.lstSubject.List(i)
       Else 
          subStr = subStr & ", " & Me.lstSubject.List(i)
       End If
    End If
Next i
WS.Range("D" & lr).Value = subStr
MsgBox "Data submitted successfully!", vbInformation
End Sub

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 UserForm 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 List Box by UserForm with Excel VBA.

Once the module is inserted, and we’ll write a subroutine to Sub 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 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.

When we click on submit button, we will get this message.

By the way the data is also filled in the cell

Go to VBE to activate multiple Selection, Go to List Box properties and select Multi selection.

Now go back to the worksheet and click on the show form button.

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.

Click on Submit Button And get Message.

Now Close UserForm. By the way the data is also filled in the cell.

Now we will again go to VBE and change the List Selection style, Here we will select the List Style Option

Now go back to the worksheet and click on the show form button.

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.

 

 

 

 

 

 

 

 

Click on Submit Button And get Message. After Submit Successfully we’ll Close Form

Data Filled on Worksheet

Therefore, I hope that you have understood Data Entry with List Box 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>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Data Entry with List Box by UserForm in Excel VBA