Excel VBA - Fill ActiveX Combobox with dynamic range from another workbook

I am trying to fill an ActiveX Combobox with a dynamic range from another workbook but I am getting a run-time error '1004' Application or object-defined error on the line of code that assigns the range:

Dim prfile1 As String
Dim prfile2 As String
Dim filepath As String
Dim checktotal As Integer
Dim checkrng As Range
Dim emunber As String

prfile1 = Worksheets("setup").Range("B10").Value
prfile2 = Worksheets("setup").Range("B7").Value
filepath = Worksheets("setup").Range("e10").Value
emunber = Worksheets("ReprintOld").Range("V3").Value

Workbooks.Open filepath & prfile2

checktotal = Workbooks(prfile2).Worksheets(emunber).Range("AE1")
checkrng = Workbooks(prfile2).Worksheets(emunber).Range(Range("U5"), Range("U5").End(xlDown))

1 answer

  • answered 2018-10-17 00:25 K.Dᴀᴠɪs

    You need to qualify all your Range objects.

    checktotal = Workbooks(prfile2).Worksheets(emunber).Range("AE1")
    With Workbooks(prfile2).Worksheets(emunber)
        Set checkrng = .Range(.Range("U5"), .Range("U5").End(xlDown))
    end with