Link to home
Start Free TrialLog in
Avatar of jobprojn
jobprojnFlag for United States of America

asked on

Excel 2013 - ListFillRange in ActiveX List Box

In Excel 2013 (October 24th RTM release) we're having problems getting the ListFillRange property of an ActiveX list box to work where the list range is derived from an Excel table.

For instance, in a worksheet create a column that contains a few records (a,b,c,d,e).  Highlight this column and make it a table (go to Insert >> Table, and name it "Table1").  By doing this you've created a table name that resides under Formulas, Name Manager.

In the Name Manager create a new named range and name it nTable1.  The "Refers to:" value should be set to "=Table1".  By doing this you're crating a named range based on a table.

Now add an ActiveX list box to your form, and under the "ListFillRange" property type in nTable1.  Notice that the ListFillRange does not retain this value, it simply deletes it.

This worked in 2007 and 2010 without issue, but it's only a problem now in 2013.  If anybody has a relatively simple workaround for this please share.  

Thanks.
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

What was the purpose of creating a second named range that referred to the first named range when you could also put in Table1 in the ListFillRange property of the listbox?  Try that in your 2013 version and see if that works?
Avatar of jobprojn

ASKER

Using the Table1 table name as the ListFillRange value did not work in 2007 or 2010, and also not 2013.  In 2007 and 2010 you had to create the second non-table named range.  This worked fine.
Strange, I actually just tried it in 2007 and it worked just using Table1, that's why I brought it up.
Interesting.  I don't have access to 2007 anymore, but I just tried it in 2010 and using Table1 does not work in that version.
Try just using the actual column range instead of the Named Range in the ListFillRange property.  For example, A2:A10

If this doesn't produce the list, then there is definitely something wrong.  This is a column list and not a row list, right?
Yes, this is a column list, not a row.  Finding the range address of the ListObject gets a bit sticky as well.  It generates the column range as required (i.e. $A$2:$B$8), but because the table has headers you have to account for that.  When we do use this as the solution we're finding this change throws other items in our VBA project off as well.  Therefore, the ideal solution is to be able to use the named range as the ListFillRange.

This is certainly a bug in Excel 2013, but Microsoft has yet to acknowledge it.  We can of course program all around this, but who wants to do that??  Not I.  Anyhow, any other suggestions are certainly welcome.  Thanks for what you've provided.
Actually, I only wanted to verify that it worked in your system using the actual range just to help identify where the problem may be. Now, can you try giving the range a different name (not Table1) and see if that works in the ListFillRange property?
This does not work.  The table is called Table1, the named range based on the table is nTable1.  Neither using the table name or named range work in the ListFillRange propery.
I meant for you to give the range itself a different name instead of Table1 and using that in the the LisfFillRange.  This is just to be sure there is no conflict somewhere that we are not aware of with another Table1.
Hmm, maybe we misunderstand.  There is not a range called Table1.  There's a table called Table1, and then a range called nTable1, but there's not a range called Table1.
You're right, I misunderstood.  You did explain it that way in your original question and I just didn't read it right.  Sorry about that.
ASKER CERTIFIED SOLUTION
Avatar of jobprojn
jobprojn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Posted own solution as this is a very unique bug that very few users are aware of.