Combobox using .additem "Permission Denied"

Hello again

I'm having a small issue with the .additem command. My combobox listfillrange is a named range and when i try to use the .additem command, it gives me a "Permission Denied". But when I try the same with a new combobox, it works just fine.

combobox1.additem "item1"

The above code runns from another combobox. Both comboboxes are in the first sheet

Please Help

I've done a quick experiment and confirmed your results.

When a Combobox is filled from a defined range on the worksheet (by setting the ListFillRange) then the VBA AddItem cannot be used.

To add an extra item to the Combobox you will need to add to the range on the Worksheet. In code this can be achieved with the following suggested lines....

Range(ComboBox1.ListFillRange).End(xlDown).Offset(1, 0) = "New Item"

ComboBox1.ListFillRange = Range(Range(ComboBox1.ListFillRange).Cells(1, 1), Range(ComboBox1.ListFillRange).End(xlDown)).Address

This assumes the ListFillRange is a simple column of values and that there is no problem with simply adding to the end of it on the Worksheet.

Alternatively, you could avoid referring to a Worksheet range for the item values, and code them directly into the Combobox with VBA.

Ah yes I guess looking back it makes sense that i can't use .additem when i've defined the .listfillrange using a range. I really like your suggestion too, i didn't know you could call the range through the combobox properties.

I have since figured out how to use arrays (and by figured out i mean i can make and call them) and i now populate the comboboxes by turning my named ranges into arrays and then using a for loop to add the array into the .listfillrange one line at a time. My code requires constant filtering of the dorp down menus and this method allows me to add and remove items. Unfortunately my sheets do not have blank rows at the bottom of each range.

Thank you very much for your help. I find my self browsing the forums just to soak up all the neat ideas that have been posted.

Yes, using arrays is useful for managing ComboBoxes and Lists.

You may want to experiment with the following ideas, where a single line could set all the values rather than having to use a loop....

Option Base 0

Sub Example()
' put range values into a ComboBox as an array.....
ComboBox1.List = Range("a1", Range("a1").End(xlDown)).Value
' OR
ComboBox1.List = Range("RangeName").Value

' get values out of a combo box into an array variable....
Dim ListContents()
ReDim ListContents(ComboBox1.ListCount - 1, 0)
ListContents = ComboBox1.List

' get values out of a combo box and into a range on the Worksheet
Range("c1", Range("c1").Offset(ComboBox1.ListCount - 1, 0)) = ComboBox1.List

End Sub