VBA - Filling Combo Box with Dynamic Range

Question

I have a problem with my ComboBox1, I am trying to make some hidden filters to make viewing lots of data easier and i want to select what i filter out via a combo box, where the input selection is part of this processed data.

Here is some code that i tried to write for populating the ComboBox but it comes up with an error saying that it is the wrong use of Method.

Sub ComboBox1_DropButton_Click()
Dim i As Range

With Sheets("Pipe 16")
Set i = .Range("G5:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
End With
Me.ComboBox1.ListFillRange = "i"

End Sub

Any help appreciated.

EDIT

this cannot be answered by the answers shown in Dynamically set ListFillRange in Excel ComboBox using VBA

Possible duplicate of [Dynamically set ListFillRange in Excel ComboBox using VBA](http://stackoverflow.com/questions/4200712/dynamically-set-listfillrange-in-excel-combobox-using-vba) — Dave, Jul 14 '16 at 15:47
Have just tried what is suggested in here and it doesnt work @Dave — dyslexicgruffalo, Jul 14 '16 at 15:48
You realize you're trying to give it the string `i`? Not whatever was set to the variable in your `With` statement? Try `Me.ComboBox1.ListFillRange = "=i"`. — BruceWayne, Jul 14 '16 at 15:51
IT still has an error with the `Me.ComboBox1.......` where it is still saying - "invalid use of Me keyword" — dyslexicgruffalo, Jul 14 '16 at 15:52
Why are you putting the code in ComboBox1's DropButton_Click event? Rather put it in the form's Activate event. — Excel Developers, Jul 14 '16 at 15:54
I read up on another forum that it would work like that but evidently not — dyslexicgruffalo, Jul 14 '16 at 15:56
here is the old forum - http://www.mrexcel.com/forum/excel-questions/410077-combo-box-dynamic-input-range.html — dyslexicgruffalo, Jul 14 '16 at 15:56

score 3 · Accepted Answer · answered Jul 14 '16 at 16:14

ListFillRange accept the range's address not the range itself.

Sub ComboBox1_DropButton_Click()

    Dim i As Range

    With Sheets("Pipe 16")
        Set i = .Range("G5:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
    End With
    Me.ComboBox1.ListFillRange = i.Address

End Sub

score 0 · Answer 2 · answered Jul 13 '18 at 07:46

0

ComboBox1.RowSource = "" ComboBox1.List = ActiveSheet.Range("B3", ActiveSheet.Range("B" & Rows.Count).End(xlUp)).Value

answered Jul 13 '18 at 07:46

Elie Wakim

1

Posting code without any explanation isn't welcome here. Would you please [edit](https://stackoverflow.com/posts/51320118/edit) your post ? – Cid Jul 13 '18 at 08:11

VBA - Filling Combo Box with Dynamic Range

2 Answers2