I have several ActiveX comboboxes (they don't have borders) that I have placed on a worksheet (Excel 2007) named "Sale Data". I have specified a ListFillRange for each combobox on another worksheet, Lists
. The ListFillRange for all comboboxes is the same. I have specified a different cell for each combobox's LinkedCell, which also are located on the Lists worksheet. My problem is that whenever I sort the ListFillRange, the previously selected values in each combobox change. It appears that there is some sort of linkage that is established from each combobox's selection to its position in the ListFillRange when it was selected. When the ListFillRange is sorted, different values appearing in the positions referenced by each combobox's selection replace the original selection.
I have attached a test worksheet that demonstartes this behavior. What I need to be able to do is have each combobox's selection not be changed when the ListFillRange is sorted. The reason I need this functionality is that the ListFillRange is defined dynamically. When items are added to or taken away from the ListFillRange the list needs to be re-sorted. That currently blows up all the combobox selections.
I appreciate any help with this problem. It's probably something simple I've overlooked.