I am having a serious problem. I have a listbox tied to a dynamic named range to load values. For some odd reason if there are named ranges it's deselecting any selecting items on calculate. I have no idea why this is happening, but before I completely rebuild my tool, any ideas?
Use VBA's 'dynamic ranges' in listobjects (Tables) or currentregion, or usedrange, or specialcells, or cells(rows.count,1).end(xlup), combined with the listbox property '.List'
How have you 'tied' the named range to the listbox?
If posting code please use code tags, see here.
Yes Listfillrange: 'DynamicNamedRange'Originally Posted by Norie
It was working for one Listbox, and then as I was coding I found it wasn't working on another. I thought it was my code, and after deconstructing everything 3 times, I found it was the named range. I deleted all listboxes and started from scratch and now only for this workbook. Listfillrange = Named Range will clear selections on recalculate
I'm not sure exactly what you're trying to say. Are you listing the possible ways to use a dynamic range?
So, it seems that it is for sure something with the listbox if calculate, then unselect all when linked to DynamicNamed range, not static named range, or static range. This is baffling. I'm new to VBA so maybe there's something I'm missing but this is a royal pain. Solutions?
Last edited by cmore; 08-31-2013 at 10:16 PM.
Dagg I feel dumb. http://stackoverflow.com/questions/4...obox-using-vba
You can used named ranges to populate a listbox, and named ranges can be dynamic.
You can also not use the ListFillRange property to popuate the listbox, and doing that might prevent the problem you describe.
For example to populate a listbox from column A on Sheet1:
This code would go in the worksheet module of the sheet the listbox is on.
Please Login or Register to view this content.
Thanks, I will try this. So the bug is I'm finding an issue of managing the items being unselected on recalculate of the sheet. I have about 8 controls and some require recalculating and it's resetting things in a way I can't find sequence to control
The problem is caused by the use of ListFillRange with dynamic named ranges for population of the listboxes.
Whenever you recalculate the worksheet the dynamic named ranges are recalculated and the listboxes reset.
Dagg, how'd you find this out? I learned this through a lot of pain, but so many moving parts, couldn't determine with certainty. THANK YOU so much man. So ultimately I need to foget the whole named range convention entirely. Problem with the ctrl+down method is my range is housed in a array operation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks