combo box value from another workbook

Question

I am creating excel macro for combo box in user form. I have succeed to create in single workbook. Here is my code in current workbook.

ComboBox1.RowSource = "Sheet1!G1:G" & Range("G" & Rows.Count).End(xlUp).Row

What I want to do now is, I want to get the value for my combo box from another workbook, lets say Project.xlsx in range column G.

Any idea what will the code be looks like?

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2017-10-16T09:02:08

You just need to prefix the workbook name in square brackets but if you just did:

ComboBox1.RowSource = "[Project.xlsx]Sheet1!G1:G" & Range("G" & Rows.Count).End(xlUp).Row

Then that would not work as you have not qualified the Range you want to work with (e.g. the one in some worksheet in the Project workbook).

The code below will find the range in column G in Sheet1 of the Project workbook and assign the values to the ComboBox in your UserForm:

Option Explicit

Private Sub UserForm_Initialize()
    Dim wbExternal As Workbook   '<-- the other workbook with the data
    Dim wsExternal As Worksheet  '<-- the worksheet in the other workbook
    Dim lngLastRow As Long       '<-- the last row on the worksheet
    Dim rngExternal As Range     '<-- range of data for the RowSource

    Set wbExternal = Application.Workbooks("Project.xlsx")
    Set wsExternal = wbExternal.Worksheets("Sheet1") '<-- change to your sheet if required
    lngLastRow = wsExternal.Range("G" & wsExternal.Rows.Count).End(xlUp).Row
    Set rngExternal = wsExternal.Range("G1:G" & CStr(lngLastRow))

    ComboBox1.RowSource = rngExternal.Address(External:=True)

End Sub

combo box value from another workbook

1 Answers