Re: Nested "filters" of a list

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi again Andrew (or whoever happens across this),

OK, so I've learned a bit about ComboBoxes and have managed to set up what
you had discussed earlier: where the defined list of ComboBox2 is based on
the selection of ComboBox1.

So, my delimma now consists of this:
I have another list to perform the same action; however, the database has
over 5000 combinations of selections.
My ? is, is there any way to reference a filter's defined list (the list
given when you click the drop-down arrow of a filter) in a macro so that the
List array of the ComboBox is that of the filter's defined list.


Thanks again,

EdE

"EdE" wrote:

Hi Andrew,

Yeah, I was afraid it was going to be fairly difficult to make it work the
way I was hoping.

I'll see what other options are available.

Thanks again for the help,

EdE

"loudfish" wrote:

My apologies for the novel I just wrote, I just wanted to make sure I was
clear, again, as I'm not the best in explaining myself.

Sorry it's taken a while to get back to you. Your description is very
clear this time - I can see your filtering requirement.

Do you really need the selections to be on a separate worksheet? With
Excel's native autofiltering, which I described above, the drop-down
options which get displayed to the user will be restricted by other
filters, just as you need (ie only displaying LA, SF, PD when CA is
selected on the City).

If you do need them on separate worksheets, then can I suggest the
following alternative solution.

1. User enters ColumnA filter in Cell A2.
2. User enters ColumnB filter in Cell B2.
3. User enters ColumnC filter in Cell C2.

Note that drop-downs in B2 & C2 do not automatically restrict based on
selection based on A2 - this is the bit of the requirements that are
not met. (I'm pretty sure it is possible to do this bit, but AFAIK,
only with quite a bit of VBA coding around combo boxes).

Once filled in all the filter criteria, you hit a "apply filters"
button, which filters the database worksheet according to the
criteria.

This can be done with advanced filters, and some simple VBA code
should give you what you need.

1. Get the Advanced Filters working how you need them. See
Contexture's description of advanced filters - http://www.contextures.com/xladvfilter01.html
(by Debra Dalgleish). It is possible to set this up so that you have
the table on one page, and the filter criteria on a different
worksheet.

2. Buttons and Automation. For your users, you can create two buttons,
linked to VBA code.

one "apply filters", including code like this:
Range("A7:D16").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Sheet2").Range("J16:L17"), Unique:=False
(make the range names tie to what you need, I would recommend using
"Named Ranges")

one "remove filters", including code like this.
ActiveSheet.ShowAllData

There is a bit more to the VBA than just these two lines, but really
just activating the right worksheets - you should be able to use the
recorder for all of this.

For the missing criteria (drop-downs which dynamically update), my gut
feeling is that you'd need combo boxes (which have defined lists
behind them), linked to a chain of on update events that update the
"defined lists" whenever any of the selections change - ie you select
USA, it triggers a filtering of the "database" and a rebuild of the
"defined lists" for the other combo boxes based on the filtered lists.
Tricky. If you want to go down this route, I'd start with a solution
that misses this bit out, then look at adding it once the other stuff
works.

HTH

Andrew





.

Relevant Pages

  • Re: Low priority MX matching primary MX to reduce spam?
    ... can be used to train your content filters. ... lists of legitimate non spamtrap email addresses. ... Such as when your legitimate machines' load ... negative net loss and at worst self defeating. ...
    (comp.mail.sendmail)
  • Re: OFFLINE ADDRESS BOOK SECURITY ISSUE
    ... The filters seem to work fine when I preview them ... The Outlook clients do not see any of the Address Lists under All ... OAB that they can access is restricted by ACL and only has one address ... GALs in order to secure off certain employees, ...
    (microsoft.public.exchange.admin)
  • Re: We should really change RSB to a moderated forum
    ... What would really help is if the two main news readers, MS and Netscape had the ability to make an exception list so people like Ratchet or Bob Keller could easily be excluded from the plonk lists. ... This means, for the main part, Russ is STUCK with FILTERLESS Google Groups ... They have a fairly short list of newsgroups on their server, and RSB (though ... register, myself, so I couldn't find any help about how to use the filters. ...
    (rec.sport.billiard)
  • Re: python desktop
    ... > David Fraser wrote: ... >>could you perhaps split the logicaldesktop.py into separate modules? ... (filtering the lists) ... The preferred method is to use the recent filters ...
    (comp.lang.python)
  • Re: ComboBox ItemData?
    ... The idea with the comboboxes was to use them as lists of choosable items. ... datasource. ... >> private void InitializeComponent() ...
    (microsoft.public.dotnet.framework.compactframework)