Solved

Sorting ActiveX ComboBox ListFillRange Changes Previously Selected ComboBox Value

Posted on 2010-11-21
12
1,631 Views
Last Modified: 2012-05-10
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
Comment
Question by:bvallanc
  • 6
  • 6
12 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:bvallanc
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:bvallanc
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:bvallanc
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:bvallanc
Comment Utility
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
 
LVL 33

Accepted Solution

by:
Norie earned 150 total points
Comment Utility
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
 

Assisted Solution

by:bvallanc
bvallanc earned 0 total points
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Closing Comment

by:bvallanc
Comment Utility
Click the link to Rolf Jaeger's article to see how he solved this problem.  I took the easy way out!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now