VBA Express : Multiple Apps - Populate a UserForm Listbox in Word From Data in an Excel File

Multiple Apps

Populate a UserForm Listbox in Word From Data in an Excel File

Ease of Use

Intermediate

Version tested with

2000, 2002

Submitted by:

Jacob Hilderbrand

Description:

This macro will fill a listbox on a Word UserForm with data from Sheets(1) in an Excel spreadsheet.

Discussion:

You want to use a listbox in Word, but the data is in an Excel file. This macro demonstrates how to get the information you need.

Code:

instructions for use

			 'You must set a reference to Microsoft Excel Object Library
 'Tools | References
 
 'Place this code in a Module.
 
Option Explicit 
 
Sub ShowForm() 
     
    UserForm1.Show 
     
End Sub 
 
 
 
 'Place this code in a UserForm.
 
Option Explicit 
 
Private Sub CommandButton1_Click() 
     
    Unload Me 
     
End Sub 
 
Private Sub UserForm_Initialize() 
     
    Dim objExcel        As New Excel.Application 
    Dim wb              As Excel.Workbook 
    Dim FName           As Variant 
    Dim x               As Long 
    Dim LastRow         As Long 
     
    FName = objExcel.GetOpenFilename("Excel Files (*.xls), *.xls") 
    If FName = "" Or FName = False Then 
GoTo Canceled: 
    End If 
    Set wb = objExcel.Workbooks.Open(FName) 
    With wb.Sheets(1) 
        LastRow = .Range("A65536").End(xlUp).Row 
        For x = 1 To LastRow 
            Me.ListBox1.AddItem (.Range("A" & x).Text) 
        Next x 
    End With 
     
Canceled: 
     
    objExcel.Quit 
     
End Sub

How to use:

Open Word.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code from above that is designated for the Module.
Insert | UserForm.
Add a ListBox and CommandButton to the UserForm from the Control Toolbox.
Double click on the UserForm and paste the code from above that is designated for the UserForm in the Code Window that opens up.
Close the VBE (Alt + Q or press the X in the top-right corner).

Test the code:

Tools | Macro | Macros...
Select ShowForm and press Run.
Select the Excel file. The data will be taken from Col A in Sheets(1) by default.

Sample File:

ListBox.zip 10.69KB

Approved by mdmackillop

This entry has been viewed 187 times.