Populating Combo Boxes At Start up

[randell.graybill]

Someone helped me with the code below and it worked while I had it placed in a spreadsheet. I tried moving it to a standard module and it will no longer work tells me invalid use of Me. How can I populate these two combo boxes at start up?

Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ComboBox1
        .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("U:\DOH.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("A2:A150").Value
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        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
    End With
    
Dim ListItems2 As Variant, i2 As Integer
Dim SourceWB2 As Workbook
    With Me.ComboBox2
        .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 SourceWB2 = Workbooks.Open("U:\DOH.xls", _
            False, True)
        ListItems2 = SourceWB2.Worksheets(1).Range("d2:D62").Value
        ' get the values you want
        SourceWB2.Close False ' close the source workbook without saving changes
        Set SourceWB2 = Nothing
        Application.ScreenUpdating = True
        ListItems2 = Application.WorksheetFunction.Transpose(ListItems2)
        ' convert values to a vertical array
        For i2 = 1 To UBound(ListItems2)
            .AddItem ListItems2(i2) ' populate the listbox
        Next i2
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
    End With
End Sub

[randell.graybill]

Additional information:

The data for the combo boxes comes from "U:\DOH.xls"

Combo Box 1 the range is A2:A200 (some of which might be blank)
Combo Box 2 the range is D2:D200 (some of which might be blank)

[randell.graybill]

This is the last bit of help I need on this project I am thankful all the help you have given me and my other projects. This one tracks what time someone gets a unit what time they log-n to start using it.

Basically all it does is open the spreadsheet U:\DOH.xls silently, Fill combo box 1 with range a2:a200 from DOH.xls and fill combo box 2 with range d2:d200 from doh.xls.

The spreadsheet should open and close silently so the user can't see it opening and closing.

Can anyone help with this?

[mikerickson]

Replace Me with the sheet you want it to refer to

 With ThisWorkbook.Sheets("Sheet1").ComboBox1

or

 With ActiveSheet.ComboBox1

[randell.graybill]

OMG I can't believe that was all I had to do wow thanks a bunch. works like a charm.

only had to make one very minor change see below. The name of the sheet changes.

With ThisWorkbook.Sheets(1).ComboBox1