Issue with listbox using dynamic range as listfillrange


New Member
Oct 4, 2010

Here is the workflow I'm trying to achieve and where my problem arises:

1. I select option A, B, or C from a group of option buttons.
2. Based on my selection, a dynamic range, dictated by the OFFSET function, is displayed in a listbox.
3. My listbox is a multi-selection listbox, so I select multiple items from my listbox.
4. I have a command button "Update" that I click to copy the selected items from my listbox to the first row of a table. This table is used as the source data for a chart.

Issue: All of this functions more or less perfectly, except that it only works if the listbox draws from a static range. If the listbox is set to draw from a dynamic range, clicking "Update" will "reset" the listbox, thus unselecting all the selections I had made thus far.

I have posted an attached spreadsheet on here to display the problem. If anyone has any idea how to solve or work around this issue, it would be immensely appreciated.

Thank you very much for your time.

BTW both listbox and optionbuttons are in a worksheet and are activeX controls.



Well-known Member
Dec 30, 2008
Office Version
If the dynamic range appears to be the problem, use VBA to make it a fixed range, either by recycling the existing range name, or by using a temporary one.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...