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:

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

Test the code:

  1. Tools | Macro | Macros...
  2. Select ShowForm and press Run.
  3. 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 184 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express