Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011

    Unanswered: Import Data from Excel to a Listbox in VBA


    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!

  2. #2
    Join Date
    Jan 2002
    Bay Area
    How about something like this:

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

  3. #3
    Join Date
    Oct 2011
    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!

  4. #4
    Join Date
    Jan 2002
    Bay Area
    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)
    Last edited by JerryDal; 11-03-11 at 12:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts