ActiveX combobox listfill range.

[Kosie123]

Hi everybody.
I have an activex combobox filled with a dynamically named range. Rhe listfill range in the combobox has the range name in it. If I add values to the bottom of the range it does not show in the activexcombobox drop down, unless I retype the same name again in the listfill range box?? If I save the sheet nothing happens but if i save and close and reopen the sheet the added values at the bottom of the list is there? Thanks a lot.

 

[Yongle]

Please post the code that you are using to populate the ComboBox
Remember to click on </> icon above post window
- paste your code into the window which appears, amend Language to VBA Code, and click Continue

 

[Kosie123]

No VBA code. The dynamically named range (=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B),1). Named "gert". gert went into the act.x combo box ListfillRange box. If I add more vaues, it doesn't show in the dropdown. if I retype "gert" in the listfillRange box then the list shows the extra values. If I close the book and reopen it, then the extra values are updated! Curious? Seems like something the Excel clever guys must look at. I subsequently discovered that this question has been asked before elsewhere but I saw no solutions. Regards.

 

[Norie]

Have you tried recalculating the workbook?

That should update the formula used in the named range.

 

[Yongle]

Code below automatically forces named range to recalculate when combobox made active

Place in SHEET module (of the sheet containing the combobox)
- right-click on sheet tab\ View Code \ paste code into the open window\ {back to Excel with ALT}{F11}
- amend ComboBox1 (both in the name of the procedure and in the line of code) if required

Private Sub ComboBox1_GotFocus()
    ComboBox1.ListFillRange = "gert"
End Sub

 

[Kosie123]

Hi Norie
Recalculating didn't work.
Hi Yongle
I stuck the macro in the sheet module. It works.
Thankyou all!
Lets call that Solved!

 

[Yongle]

Glad that sorted it for you