Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1854
  • Last Modified:

Sorting ActiveX ComboBox ListFillRange Changes Previously Selected ComboBox Value

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.

Thanks,

Bill Vallance
ComboBox-Sort-Problem.xlsx
0
bvallanc
Asked:
bvallanc
  • 6
  • 6
2 Solutions
 
NorieData ProcessorCommented:
Bill

I don't think there's a simple way to do this.

By sorting the range that is the source of the items in the comboboxes you are likely to trigger the change event.

If all of these comboboxes/ranges are linked then triggering one might trigger the others, and so on...

Have you tried programatically populating them?

It would require a little more work but if done correctly should help.

By the way why are all the comboboxes populated from the same range?
0
 
bvallancAuthor Commented:
Thanks for the response, imnorie.  I had considered that this might be the problem.  The reason that each combobox references the same ListFillRange is that in the application where I'm using these comboboxes each row represents an individual product selection.  Each combobox presents the user with the Product Catalog (the ListFillRange that all comboboxes have in common) so they can select a product to add to an Order.

Anyone else have any thoughts?
0
 
NorieData ProcessorCommented:
Why do you have mutliple comboboxes if each item represents an individual product selection?

Usually mutliple comboboxes based on the same data are used in some sort of cascading/dependent scenario.

For example you have one combobox with the product name, then another for colour, one for size and so on.

When an item is selected from the 'parent' combobox the items in the 'child' reflect that choice...

For example when ProductX is selected the items in the colour combobox are the ones available for that product.

That's actually what I thought you had initially until I had a closer look (took me a while to find the controls).

Is that what you are actually trying to do?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bvallancAuthor Commented:
That's not what I'm trying to do.

Think of each row in the test spreadsheet that I submitted as a single product that is a part of an overall order.  Each row in the spreadsheet is completed by the sales team to form an order.  I used the combobox control because I could display multiple columns when the dropdownbutton is clicked so that my sales team could have more than just the Product Code to use to select each product in the order (TRUST ME ON THIS - salespeople need more than a Product Code alone to specify products on an order correctly!).  I have some fairly complex bundling VBA that enables the sales team to specify how each product contributes to a bundle, which products should be displayed as line-item pricing, etc.  I didn't include any of that logic in the test spreadsheet because I'm not having any problem with it.

I originally used VLOOKUP to enable the selection of each Product Code on each row.  That's a lot easier to implement than multiple comboboxes and it works great.  But, that method can't display multiple columns.  So, having worked with the combobox extensively I selected that ActiveX control to solve the problem for me.

Maybe ther's another, better, approach than the comboboxes to display a Product Code, Product Description, and Product Type (the 3 columns that get displayed with the comboboxes) when the combobox is clicked?

Hope This Helps,

Bill Vallance
0
 
NorieData ProcessorCommented:
Bill

Sorry but I still don't see why you would want multiple comboboxes.

Try a listbox, then they won't need to use the dropdown and you can display multiple rows/records and multiple columns for each row.

You could add another listbox so the user can select a records/product from the first, add it to the second and build up a bundle.

PS You could display multiple columns with VLOOKUP.
0
 
bvallancAuthor Commented:
What is the syntax to display multiple columns using VLOOKUP?  If I had know that was possible Id never have used multiple comboboxes.

Thanks,

Bill Vallance
0
 
NorieData ProcessorCommented:
Bill

Well it really depends what you mean by multiple columns and how the data is structured.

I'm still not sure what you are actually looking to do.

I've mocked something up with one combobox and VLOOKUPs.

Might be worth a look but I'm pretty sure it's not what you are looking for, might give you some ideas though.
EE---23Nov2010---ComboBox-Sort-P.xlsx
0
 
bvallancAuthor Commented:
Thanks, imnorie.  You are right - what you mocked up won't work for me.  I've enclosed a screen shot of the product configurator that I'm creating.  You can see from this image that each of the 20 products has a combobox in the "Product Code" column (these comboboxes are what I attached to my question).  The salesperson clicks each combobox to select one product for each row of the product configurator.  Using the file I attached to my question you can see how the salesperson can see the Product Code, Product Description, and Product Type when they click each Product Code combobox.  I need these three pieces of product information displayed every time the salesperson clicks on a Product Code cell.  The combbox and listbox are the only ActiveX controls that I am aware of that allow the display of multiple columns of data from a single mouse click.

Any other ideas?
Product-Configurator-Screen-Shot.jpg
0
 
NorieData ProcessorCommented:
I must be missing something here.

Why can't you use VLOOKUPs?

You could just use basically the same formulas that are in the attachement, just adjust them to work across rather than down and change the reference for what to look up to work down the rows.

You've just mentioned listboxes, have you tried them?

I know you can display mutliple columns in comboboxes but they aren't really designed for that, and personally I don't think they look that good.

As for multiple comboboxes with multiple columns, that isn't really a good idea either.

Having multiple controls of any sort can make things difficult when actually trying to do something with them.

How about using Data>Validation... for the list of products and VLOOKUPS for the data?

I'll see if I can mock something up closer to that image, thought it would be nice to have a file to work with.:)

Just another thought - a userform.
0
 
bvallancAuthor Commented:
Imnorie, I solved the problem.  Here's what Microsoft has to say about sorting the contents of named ranges:

'Defined names refer to a cell reference and not to the cell contents specifically. When performing a Data Sort, you are only sorting the contents of the cells. Because of this, a defined name may not be referencing the same data after a sort.' (Thanks to Rolf Jaeger at http://www.eggheadcafe.com/tutorials/aspnet/28ff2b12-6318-40b5-96cc-a12f2f24610e/excel-sort-command-and-na.aspx for this enlightenment).

So I give up!  I'll just fix the problem by telling my Finance department that every time they change the Product Table that they will have to re-publish the product configurator as a new version.  This is what would happen in practical use, anyway.  I appreciate all of the suggestions that you have given me.

Regards,

Bill Vallance
0
 
NorieData ProcessorCommented:
Bill

I'm not sure if the problem was named ranges, you probably could have done what you wanted without them anyway by populating the comboboxes with a different method.

One that wouldn't be affected by changes caused by sorting or whatever.

Anyway, glad you've found a solution - that's the important bit.:)
0
 
bvallancAuthor Commented:
Click the link to Rolf Jaeger's article to see how he solved this problem.  I took the easy way out!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now