Import Data from Excel to a Listbox in VBA

Pumbachero · #1 (**permalink**) 10-24-11, 04:27

Hi,

Iam Working with VBA in Excel and trying to import a list of Numbers into a Userform (Listbox). The userform is a search-formular wich is connected to a database and generates a Report in Excel with the asked Data.

Now how can i import a list of numbers from excel into the Listbox so that the numbers can be searched in the Database and Displayed in the Report?

I have already the command to open some File:

Private Sub CommandButtonIMPORT_Click()
ChDir "\"
ChDrive "c:\"
Datei = Application.GetOpenFilename("Micrsoft Excel-File (*.xlsx),*.xls,Textdateien (*.txt),*.txt")
If Filename = False Then Exit Sub

Now i need to import the numbers from excel or text-file to that box!

I would appreciate if somebody could give me some help!

JerryDal · #2 (**permalink**) 10-30-11, 23:42

How about something like this:

Code:

Private Sub UserForm_Activate()
Dim j As Long
    
    j = 1
    Do While Sheets("Sheet2").Cells(j, 1).Value
        j = j + 1
    Loop
    
    Me.ListBox1.RowSource = "Sheet2!A1:A" & (j - 1)

End Sub

Pumbachero · #3 (**permalink**) 11-03-11, 11:09

Hi JerryDal,

thank you for your reply. Now iam an absolute beginner with VBA. Where should i insert the code?

In the Formular - User Form or in Functions and in which place?

Thanks for your help!

JerryDal · #4 (**permalink**) 11-03-11, 12:42

Put the code in your Userform_Activate event.
- Pull the form up in design mode
- Left-click any blank area of the form
- Choose View Code

This may open up the form's Click_Event if you have not already created an Activate_Event. If you find you are in the Click_Event, change the letters "Click" to "Activate".

- Paste the 6 lines of code into the Activate_Event.
- Edit the code to work with your list box name
- Change "Sheet2" to the name of the worksheet where you store
the numbers to populate the list box
- In the code "Cells(j, 1)", the "1" represents column A. If the numbers
are in some other column, change the "1" to that column number.
- If your numbers do not start on row #1, for example, if you have a heading
on row #1, change "j = 1" to "j = 2" (without quotes)

Internet Services

Web Development

Digital Marketing

Consumer Tech