Active X Combo Box with Range List

[nytwodees]

I have attached an unprotected worksheet that was created in Excel 2010. This worksheet allows the end user to provide criteria in multiple columns to filter the worksheet's data by the rows that fulfill the criteria entered.

The other method is allowing the end user to supply criteria via Active X Combo Boxes. The only combo box I am having difficulty with is Total Number of Hospital Beds. This is the only combo box that supplies a list that have different RANGE selections. Unfortunately, I have not found a solution that interprets the items drop down DESCRIPTIONS into its SEARCH criteria. Please help!

[bakerman2]

See if this works for you.

Select the combination you want in both combo's of Total Hospital Beds and apply filter.

[nytwodees]

Thanks for your rapid reply. Unfortunately your solution was not what I was looking for.

The combo box drop down list should show these various choices:

DESCRIPtION IN DROP DOWN COMBO BOX

TOTAL HOSPITAL BEDS

(=> 450) Range from 450 or More
(<450) Range Less than 450
Range from 201 to 300
Range from 301 to 400
Range from 401 to 500
Range from 501 to 600
Range from 601 to 700
Range from 701 to 800
Range from 801 to 900
Range from 901 to 1000

As you can see, only the 1st two choices are 1 directional (>= or <=)
All the remaining choices are bi-directional (>= but <=) a limited PRECISE range.

SEARCH CRITERIA for the DROP DOWN COMBO BOX

TOTAL HOSPITAL BEDS

>=450
<=449
>=100 but <=200
>=201 but <=300
>=301 but <=400
>=401 but <=500
>=501 but <=600
>=601 but <=700
>=701 but <=800
>=801 but <=900
>=901 but <=1000

So I am wanting to SHOW the DESCRIPTIONS (listed above), however,
I want the SEARCH CRITERIA for each of these DESCRIPTIONS to be executed.
This would be my IDEAL solution.

The other solution would be the DESCRIPTION and SEARCH CRITERIA would be identical in the DESCRIPTION. By this I mean, I mean the DESCRIPTIONS would show the following:

(>=450
<=449
>=100 but <=200
>=201 but <=300
>=301 but <=400
>=401 but <=500
>=501 but <=600
>=601 but <=700
>=701 but <=800
>=801 but <=900
>=901 but <=1000

Items 3 onward are NOT in the correct format to accomplish the filtering I want. What is the correct format (if any)?

I hope I have been clear!

[bakerman2]

Descriptions as you want them with VBA to monitor combobox listindex.

Please Login or Register  to view this content.

[nytwodees]

Your solution works perfectly! Thanks for your knowledge, effort, and time.

I would like to know if this change is possible for Row 2:

Hospital/Medical Center/Clinic Name - County - Region - Total Hospital Beds - Total Hospital Beds .......................................

Change to:

Hospital/Medical Center/Clinic Name - County - Region - Total Hospital Beds - Total Hospital Beds
...................................................................................Range FROM......-......Range TO

If I make these changes, the filtering fails (displays no records).

Please let me know if and how to make this change. This final change will complete my project!

Thanks

[bakerman2]

This can't be done because the filter headers need to be exactly the same as your database headers.

Otherwise the AdvancedFilter won't work (as you have noticed).

[nytwodees]

Thanks again for your rapid reply.

I do remember that requirement, but I wasn't 100% sure.

A workaround I used was to add Range FROM and Range TO in the 1st Row in cells above the Total Hospital Beds cells in Row 2.

Thank you very much for helping me with my Project. I could not have been able able to complete it without your help.

Dan

[bakerman2]

Glad to help.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.