ListBox Source in Userform

[johnpants]

Hi, I am creating a sales forecasting tool which will be updated regularly and am using UserForms to enter the data. I have the form created and everything is working correctly, apart from I cannot link the Listbox's to the data on the spreadsheet. I haven't names the ranges as from what I find, if someone was to add another record to the range, it would not be included in the range without manually renaming it.

The source data is located on a seperate excel file in the same folder.

File=Admin
Worksheet=Data
Range=E5:E200
Userform=SalesEntry

could anyone tell me the required code to get this data to appear in my ListBox please? Would be much appreciated.

Thanks.

 

[johnpants]

Ok, I have just found this code that is supposed to do exactly what I want, but it brings up a runtime error when I test it... Does anyone know where I am going wrong?

Private Sub SalesEntry_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ListBox1
.Clear ' remove existing entries from the listbox
' turn screen updating off,
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("D:\Work\DrFoster\Admin.xls", _
False, True)
ListItems = SourceWB.Worksheets(Data).Range("E5:E200").Value
' get the values you want
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
Application.ScreenUpdating = True
End With
End Sub

I've coloured the sections that the error selects. Also, I have created this in a new Module. I am fairly new to this so please bare with me if it is a blatant mistake..

Thanks.