How to populate a list-box in a user-form using arrays
The complete process with the VBA code is given below:
- Click on the Developer tab
- Select Visual Basic form the Code command group
- Click on the View tab in the Microsoft Visual Basic editor window and view the earlier form by clicking on Object
- We view the earlier code for the command button
- We add another label with the caption ‘Mobile Services used’ and a listbox called ListBox2 to the earlier user-form
- We double-click our form and select the procedure Initialize and add the code for populating the listbox using an array as shown below:
Private Sub UserForm_Initialize()
ListBox2.List=Array(“Tata”, “Vodafone”, “Airtel”, “Other”)
End Sub
- Click on the run button
- The user-form fires up and the second listbox is populated with the names of the mobile services providers
- We close the user-form
- Next we add a header called ‘Services Used?’ in our worksheet
- Now we code our command button by adding a line of code to transfer the data from listbox2 to the Excel worksheet
The final code of our command button now looks as follows:
Private Sub CommandButton1_Click()
Range(“A6”) = TextBox1.Text
Range(“B6″0 = ListBox1.Value
Range(“C6”) = ListBox2.Value
End Sub
This Excel training video describes how to use an array to populate a list-box in a user-form with relevant data.
Further reading:
Using Arrays to Populate Listbox
I have seen your several videos in YouTube, I am really impressed with your presentations.
I am facing problem in calculating few data, Appreciate if you could help me.
I like to send a spreadsheet with details description.