Populate Excel User Form Combo Box with Unique Items from a column in a filtered table using VBA
Posted on 2011-03-24
I have a spreadsheet that contains data pertaining to items held in stock. Each item has a part number and a decsription and then various attributes such as colour, size, supplier etc. There are approx 1000 items (rows) and the attributes etc. take up 20 columns
I want to be able to use an Excel User Form with combo boxes to assist the user in "intelligently" identifying the best item number to request from stock given that he/she may know the attributes but not the item number. It's intended that there will be 5 or 6 combo boxes and each box will relate to one of the key attributes and that the drop down selections on the remaining boxes will be narrowed down after each successive selection.
It seems to me that the best way of doing this is to use the combobox values to create autofiltered data, using the results of each successive combo box selection to further filter on the available data until the last combo box is the actual description of the item required (from which the item number can be deduced).
I'm OK with creating the autofilters from combo boxes but I've hit a problem.
I want the combo boxes to only populate with the unique values of a visible range instead of all the values. In other words if the first combobox filters on all the "BIG" items this will leave filtered output of 30 rows with a mixture of colours being say, "RED", "BLUE", "YELLOW".
If I try and populate the "Select Colour" Combo box I get all 30 colour values. What I really want is the unique values in the visible range (column) so that the user only sees choices of RED, BLUE, YELLOW in that drop down list in the combo box.
I could probably do this by copying the values to a sheet and manipulating them there but that seems inelegant and time consuming for multiple operations and for some reason an array is in my mind.
Can anyone assist me in creating the VBA that will:
1) Allow me to efficiently detect the unique values in a column of visible autofiltered cells
2) Allow me to then populate a combo box with the values collected during the identification of the unique values.
Many thanks for your help