Excel ComboBox Simple Question

[Zubinsum]

I have a combobox that does not update when the named range to which it refers is updated.

Is there a command to reload or update a ComboBox in VB Excel? If so, could someone please provide the exact syntax?

 

[RobBroekhuis]

You mean the list items don't change? They should, automatically. How are you changing the list range, manually or programmatically?
Rob

 

[Zubinsum]

I am changing the list (inserting a cell into the named range) through a macro. I should mention, the named range is on a different worksheet (in the same work book).

In any case, the combobox doesn't update. You can try it yourself.

Do you know a command to update the combobox?

 

[RobBroekhuis]

Ah - I didn't ask. Is the combobox on a userform or embedded on a worksheet? From your previous response, I imply the latter. My first response referred to the former. Could you confirm?
Rob

 

[RobBroekhuis]

I just checked the combobox control type entered using the "control toolbox" toolbar. It, also, automatically updates when the range changes.
Rob

 

[Zubinsum]

Yes, it is the later.

The combobox will update if the named range is on the same sheet, but there seems to be a bug that doesn't allow it to update when it is on a seperate sheet.

It does however update upon reloading the workbook.

 

[Zubinsum]

By the way. Thank you for your reply. All input is appreciated.

 

[rjharr1s]

Is there a way to reload the workbook without exiting the application?

 

[xlbo]

I reckon this has got to be to do with how the range updates itself when you insert a row
It may be a better idea to iterate thru the list and use the additem method ie
Combobox1.clear
for each c in range("NamedRange&quot
combobox1.additem(c.text)
next

This really doesn't take very long

The other way round is possibly to use a "Dynamic" range name ie goto insert > Names
Put a name in and then where you would normally put a range ref, enter:
=OFFSET(Sheetname!$A$1,0,0,COUNTA(Sheetname!$A:$A)-1,1)
Where your list is in column A, starting in A1

This will update as you enter data at the bottom of the list and you will therefore not have to insert a row

HTH Rgds
~Geoff~