Let's make a start.
The basic idea is:
There is a set of possible outcomes (the data in the lookuptable)
Every time the user makes a choice the set of possible outcome will be reduced based on the user's choices.
There are several ways of filtering data.
The more you use filtering in variables (instead of using a sheet) the faster the code will be.
That's why I decline the methods .AutoFilter or .AdvancedFilter.
Approach:
You can make a variable-based database using a String.
The only thing you have to determine is a separator for records and a separator for fields.
In this case I use vbCr as Recordseparator and pipeline ("|") as Fieldseparator.
You can split a String into a database of records using the VBA-method Split (what's in a name ?) and the recordseparator.
database=split(string,vbcr)
Having done that the result is a 1-dimensional Array.
VBA has a built-in method to filter arrays: filter(array, criterion)
So it will be very simple to filter that 1-dimensional database, based on the user's choices.
Now 2 problems are left:
1. how do we convert the data in the sheet 'lookup table' in to a string
2. how do we present the filtering result in the combobox.
ad1.
- first we read all the data into a variant array sq
- then we read the values in all cells, row by row into the string c01, using pipelines to separate fields and vbCr to separate records.
- because we want to do this exercise only once we declare c01 to be a private variable, so that we can use it's content in other procedures (Combobox1_Change) in this module (sheet1) as well.
- The first choice that has to be made is a choice from the options in column A; This means that every time the user starts afresh those options should be shown in Combobox1.
Because you preferred not to have duplicates we use the most simple VBA-method to create a unique list by putting those unique values in a string (c02).
And because we want other procedures in this module to use this unique list too we declare this string as a private variable.
The unique list from column A is assembled by:
Combobx1 will be populated with these options by:
ad 2.
The respective choices the user makes have to be stored, because we need to filter the database based on all previous choices.
We could do that in a distinct variable, but I prefer to use one of the Combobox's properties. That has already been loaded in memory so why wouldn't we. The property .althtml serves this purpose. Each choice will be strored here seperated by the fieldseparator, so that we can us this string as filteringargument.
After the user has made a choice we have to do the following:
- add the new choice to those already stored in .althtml
- use the property .althtml to filter the database
- create a new list of unique options in the next (x+1) 'field' of the database adn populate teh Combobox with it
- if only 1 record is left in the filtered database it's nu use fatigueing the user any longer, so all the fields will be shown.
The rest of the code has to do with
- writing to the sheet (not obligatory),
- preventing redundant looping
- formatting
To grasp the VBA methods split, filter, application.transpose, replace, resize, etc. I can't explain better than the VBEditor's help.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks