Combo box properties in VBA
 

I've created a Combo box from the Control toolbox. In ListFillRange I have
entered a range name rather than specify the cell references. So far so good.
Everything works.
However if I convert that named range to a dynamic range i.e one that
expands as new rows are added to it (using OFFSET and COUNT) the list of
choices that appear in the drop down list become slightly incorrect with one
of the items appearing twice even though it appears only once in the
specified range.
As an alternative I've tried programmatically deleting the range and
recreating it as an expanded range. Unfortunately, in order for this to work
I have to manually open the Combo box and re-insert the range name in the
LIstFillRange even though it is the same name as before. Is there a way of
using code to emulate the process of opening the combo box and re-entering a
name in the ListFillRange field.
Thank you

Combo box properties in VBA
 

Hi,

To refresh the list, change control and range reference to suit.

Activesheet.OLEObjects("Combobox1").listfillrange= "MyList"

Cheers
Andy

Ben wrote:
I've created a Combo box from the Control toolbox. In ListFillRange I have
entered a range name rather than specify the cell references. So far so good.
Everything works.
However if I convert that named range to a dynamic range i.e one that
expands as new rows are added to it (using OFFSET and COUNT) the list of
choices that appear in the drop down list become slightly incorrect with one
of the items appearing twice even though it appears only once in the
specified range.
As an alternative I've tried programmatically deleting the range and
recreating it as an expanded range. Unfortunately, in order for this to work
I have to manually open the Combo box and re-insert the range name in the
LIstFillRange even though it is the same name as before. Is there a way of
using code to emulate the process of opening the combo box and re-entering a
name in the ListFillRange field.
Thank you

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Combo box properties in VBA
 

Thank you that does the trick.

"Andy Pope" wrote:

Hi,

To refresh the list, change control and range reference to suit.

Activesheet.OLEObjects("Combobox1").listfillrange= "MyList"

Cheers
Andy

Ben wrote:
I've created a Combo box from the Control toolbox. In ListFillRange I have
entered a range name rather than specify the cell references. So far so good.
Everything works.
However if I convert that named range to a dynamic range i.e one that
expands as new rows are added to it (using OFFSET and COUNT) the list of
choices that appear in the drop down list become slightly incorrect with one
of the items appearing twice even though it appears only once in the
specified range.
As an alternative I've tried programmatically deleting the range and
recreating it as an expanded range. Unfortunately, in order for this to work
I have to manually open the Combo box and re-insert the range name in the
LIstFillRange even though it is the same name as before. Is there a way of
using code to emulate the process of opening the combo box and re-entering a
name in the ListFillRange field.
Thank you

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info