How do I update contents of activeX combobox?

[Paul Silverman]

Hi Folks,

I have an activeX combo box which is populated by the contents of a range
called "ClientList". That works fine, and includes all items in the range.

When I add a new line to ClientList and rename the range, the update is not
reflected in the combo box until the workbook is closed and then re-opened.

Is there a way to correctly repopulate the combo box with the new updated
ClientList without closing and opening the workbook?

Thanks in advance,

Paul Silverman
Melbourne Australia

 

[K Dales]

You need to get an event procedure (triggered by the
change in the range name) to reset the combobox
ListFillRange property. But I don't know of any event
triggered just by renaming a range. However, I found a
trick: create a formula somewhere that does some
calculation based on your ClientList range (I just filled
a cell with the formula "=MAX(ClientList)"). Now changing
the range name forces a recalc of the sheet; use the
Workbook_SheetCalculate event to reset the combobox:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Sheets("Sheet1").ComboBox1.ListFillRange = "ClientList"

End Sub

This seems to work for me...

K Dales