Originally Posted by
mdmackillop
Slightly revised ... and tested!
[vba]
Private Sub UserForm_Initialize()
Dim ListItems As Variant, sh As Long
Application.ScreenUpdating = False
'Call FillList(Me.ListBoxName, Workbook Path & Name, SheetNumber, RangeAddress)
Call FillList(Me.ListBox5, "C:\Temp\Rates.xls", 1, "A1:A20")
Application.ScreenUpdating = True
End Sub
Private Sub FillList(Lst, WBook As String, Sht As Long, Rng As String)
Dim SourceWB As Workbook, ListItems
With Lst
.Clear ' remove existing entries from the listbox
Set SourceWB = Workbooks.Open(WBook, False, True)
ListItems = SourceWB.Sheets(Sht).Range(Rng).Value
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
.List() = Application.WorksheetFunction.Transpose(ListItems)
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
End Sub
[/vba]