Selected Answer
Leopard,
I think the first problem in ComboBox1_Change() is that you set i as a Range but, in fact, the .ListFillRange property is a String (which describes a range).
In the attached spreadsheet Combobox listfill..., this is shown as follows... You'll see a light blue ComboBox in column C. If you click the Down arrow, you will see the list reflects (originally) B2:B4 (i.e. -, a, b and c). (If you pick a value, this simple macro will confirm your selection:
Private Sub ComboBox1_Change()
MsgBox "User chosen value= " & ComboBox1.Value 'Confirm the user's choice after click
End Sub
and I guess you'd expect the Change sub to do something with the value selected, not to modify the ComboBox).
The following static sub can be run to show that the string approach works. Run it from the VBA Editor and you'll see the range changes to cover B2:B9 (end of used range in B). If you add values in B10 etc. and run the macro again, it should extend the Listfill to include any new values.
Private Sub Set_CB_List()
Dim i As String
i = "B2:B" & Range("B" & Rows.Count).End(xlUp).Row ' Form a string, detecting end of range in column B
ComboBox1.ListFillRange = i 'Set the listfill proprty to the new string
End Sub
Now while that works in by a static way, it doesn't seem to work dynamically e.g. on the ComboBox1_DropButton_Click() event.
However I found the article on another website Populate a userform combo box in Excel with a dynamic list
Revision 2: I have now tried to follow the instructions in that link and it works (with correctly named sheet, ComboBox and Range Name), adding new colours added to the range Revision 3 It now has a Command Button which launches the UserForm (where the Combobox list changes dynamically in size and content as expected) - see second attached file Dynamic list in ComboBox with button v0_b.xlsm
Credit go to the authors of that webpage naturally but if this works, kindly mark this Answer as Selected
Revision 4: If your data is stored in a table, an alternative approach is suggested by Don's video tutorial Automatically Updating Dependent Drop Down List Menus in Excel.
The first 3 minutes of that illustrate that a table "extends itself" for data validation purposes (in the video) but it also works for a Combobox :-).
In the attached file Combobox listfill from table test v0_b.xlsm, I've written some data in cells E3:E9 then selected them and used "Insert Table" to convert them to a table (with a header row). I then created the pink-purple Comboxbox to the right and in Properties, set ListFillRange to E4:E9.
That populates the ComboxBox with that range BUT if you enter a value in the next cell E10, that is added to the list (and the ListFillRange now appears as E4:E10).
