Populate ListBox on User Form Using Arrays

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

Leave a Reply

Your email address will not be published. Required fields are marked *