How do I update contents of activeX combobox?
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 |
How do I update contents of activeX combobox?
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 -----Original Message----- 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 . |
All times are GMT +1. The time now is 05:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com