Excel Setting ListFillRange Property in Property Window - not VBA ISSUE

Question

In the properties window of an ActiveX ComboBox, I am trying to put a named range into the "ListFillRange" field. The only two things that the property window is allowing me to put there is either an absolute range: A2:A14 or a named range that is linked to an absolute range (like the three named ranges I marked in green in the screenshot:

All the other named ranges in the screenshot, when I put them into the "ListFillRange" field and click 'ENTER', the field deletes itself and won't accept it.

The range I need there is marked in yellow in the above screenshot ("rng_HelperNameList_clients") which is linked to the following formula:

=CLIENTS!$J$2:INDEX(tbl_clients[@Searchable],COUNTIF(tbl_clients[@Searchable],"?*"))

I also tried to do this in VBA:

Private Sub CmboBox_SearchClient_main_Change()
CmboBox_SearchClient_main.ListFillRange = "=rng_HelperNameList_clients"
CmboBox_SearchClient_main.DropDown
End Sub

But although I don't get an error, the ComboBox does not populate with the relevant data that the formula should be feeding it. I know the formula works because the formula is working on cells on a sheet.

How can I put a named range that's linked to a formula directly into the "ListFillRange" field in the ComboBox properties window OR get the VBA to work?

UPDATE:

I figured out how to trick the ComboBox Properties. I changed the named range to link to a random absolute range ($A$1:$A$14) which then allowed me to put the named range into the "ListFillRange" field. Then I went to the named range and changed the link to my formula and the property window still had the named range in the "ListFillRange" field! The only problem now is that the combobox is not showing the results of the formula even though the cell on the sheet that has the same formula is. But this is a different issue and I think I'll create a new question for this.

Zvi Twersky Zvi Twersky · Accepted Answer · 2018-12-13T06:22:44

Please see my UPDATE in my question for a work-around to this issue. I also got the combo box to work. Problem was that it was not part of the table that had the data and needed to get some "last-row-with-data" code like an INDEX, LOOKUP, ROW, etc. I used INDIRECT to get the last row of data.

This was my final string for the named range that is the ListFilledRange:

=CLIENTS!$L$2:INDEX((INDIRECT("CLIENTS!$L$2"&COUNTA($L:$L))),COUNTIF(INDIRECT("CLIENTS!$L$2"&COUNTA($L:$L)),"?*"))

Excel Setting ListFillRange Property in Property Window - not VBA ISSUE

1 Answers