Excel VBA - Populate ComboBox with dynamic range

[MilkyTech]

I am trying to populate an ActiveX ComboBox with the values from a dynamic range from another workbook which I have set a variable for. The variable is returning the dynamic range properly but I can't figure out how to get these values into my combobox. Everything I try gives me a different error code. This is my latest attempt:

Dim prfile1 As StringDim 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
Windows(prfile2).Activate
checktotal = Workbooks(prfile2).Worksheets(emunber).Range("AE2")


With Workbooks(prfile2).Worksheets(emunber)
    
    Set checkrng = .Range(.Range("U5"), .Range("U" & 4 + checktotal))


End With


Windows(prfile1).Activate


ComboBox1.RowSource = checkrng

 

[Norie]

For RowSource you need the address of the range.

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

 

[MilkyTech]

with that I get Object Required error

 

[Norie]

What was the previous error?

 

[MilkyTech]

It was Object Required previously also when trying RowSource or List. this is just a portion of the code in the module. I am somewhat new to VBA and maybe I don't even want a combobox. I just need a dropdown to populate with the values in the dynamic range. After I get this figured out, I will then need to figure out the code to copy and paste a row based on a selection in this dropdown.

 

[Norie]

Where is the code you posted located?

If it was in the module of the sheet ComboBox1 is on then you would be able to directly reference the combobox by name, as you are doing.

If the code is not in that module then you'll need to use another method to refer to the combobox.

 

[MilkyTech]

it is not in the sheet module. it is in another module that runs a bunch of other code.
I have a listbox on this sheet ("ReprintOld). In the listbox is a list of employees. When an employee is selected some other data on the sheet is changed but I now want it to also fill a dropdown with a list of all check numbers from that employee. Since starting this thread, I have gotten that list of check numbers to be copied and pasted onto this sheet. perhaps this will make it easier to populate the combobox.

 

[MilkyTech]

ok I got it to fill the combobox using the pasted list like this:

With Workbooks(prfile2).Worksheets(emunber)    Set checkrng = .Range(.Range("U5"), .Range("U" & 4 + checktotal))
    checkrng.Select
    Selection.Copy
End With


Windows(prfile1).Activate
Sheets("ReprintOld").Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


 ActiveSheet.Shapes("ComboBox1").Select
    With Selection
        .ListFillRange = "ReprintOld!$U$2:$U$20"
    End With

But there are a couple of issues with this. its not really dynamic (the size of the range on the other workbook increases with every check I write) and the first line of the combobox (the visible line) is blank. I don't want the user to be able to type an entry, just list the check numbers starting at the top of the combobox. And I know this following code won't work, but is there a way to have a dynamic range with this type of method? something to this effect:

.ListFillRange = "ReprintOld!$U$2:Range(Range("U2"), Range.End(xlDown))"