Thread: populate combo box from range in different workbook

Hi Everyone

VBA novice but learning. I have 2 workbooks one called stations and the other services. I would like to use a combo box in services showing the values from column G in stations. Depending on the selection made would then update the values from stations A1 to services C7, stations A3 to services C8 and stations A6 to services C9. There are a few more but Im hoping with a push in the right direction I can manage the rest.

Having problems, can only get values into combo box with both books open.

Any help appreciated.

Regards
brendonh

You need to open the other workbook, load the combobox then close the other workbook. Hise this by switching off screen updating

Option Explicit

Private Sub UserForm_Initialize()
    Dim oWbk   As Workbook
    Dim rdata  As Range

    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not WorkbookOpen("P:\Accounts\Billing Schedule.xls") Then
        Set oWkbk = Workbooks.Open("P:\Accounts\Billing Schedule.xls")
    Else: Set oWkbk = Workbooks("P:\Accounts\Billing Schedule.xls")
    End If
    'this is the data to load to combobox
    Set rdata = oWkbk.Worksheets("MASTER SCHEDULE").Range("A5:A103")

    With Me.ComboBox1
        .Clear    'clear any previous data
        .Style = fmStyleDropDownList
        .BoundColumn = 0
        'set RowSource
        .RowSource = rdata.Address(external:=True)
        '0 = no selection
        .ListIndex = 0
    End With


    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True
End Sub

It's for a combobox on a userform. What kind of combobox are you using & where is it?

If you use an ActiveX combobox you can load the whole sheet in workbook 'stations' into that combobox.
Dependent of what you want you can show all data or only some columns. You can make changes in the combobox's values. and when done write those result directly into the workbook 'stations'.

My code will not work with forms comboboxes. You really need to make sure that you give us as much information as possible.

To read more about how to use a userform as a database entryform look here.

See the DatabaseForm