Use Combo Box To Define Which Column To Autofilter

[SamuelT]

Hi all,

Same project as yesterday...different problem!

I have a list of dates relating to various stages in a project. What I'd like to do is when a button is pressed on the spreadsheet a user form pops up - this is done, no problem. On the userform I have two sets of data input - the first is a combo box which displays the various stages of the project (i.e. column headers). The second is a text box where the user enters a date - dd/mm/yyyy.

On the worksheet I have an autofilter across the header columns. What I'm trying to do is when the 'Search' button is pressed, get the code to run the autofilter on a column depending on which entry is chosen from the drop down on the combo box. e.g: if 'Feasibility Upload' is chosen the autofilter on column B is run, if 'RD Sign Off' is chosen the autofilter on column H is run, etc.

The date part isn't such a biggie. I'm going to use something along the lines of:

Please Login or Register  to view this content.

However, the line:

Please Login or Register  to view this content.

...needs to be dependent on the combo box. One last thing, if the date isn't in the column I'd like a message to come up saying something along the lines of "Date not found".

Hope that makes sense. Any help greatly appreciated.

TIA,

SamuelT

[stevekirk]

hi SamuelT,

would it not be possible to set up a table with your header and details against them would be the coulmn number IE 1,2,3 etc for column a,b,c
then use a vlookup from the results of you combo box

this code will then filter the correct column

Please Login or Register  to view this content.

?
steve

[SamuelT]

Hi Steve,

Thanks for the suggestion. If possible I really need to keep the current table of data rather than transfer it anywhere - it's already been used by a fair few paeople.

Cheers for getting back to me though,

SamuelT

[stevekirk]

hi SamuelT,



i was not suggesting moving the data. i assume both the combo box and the input box are working if the combo box in looking at the column headings i assume they are in a list. in that list is it not possible to put the column numbers next to the headings. ?

steve

[SamuelT]

Hi,

OK - prepare for a laugh: my VBA is very poor at best. This is what I've come up with so far:

Please Login or Register  to view this content.

Now, this doesn't work but hopefully you can see what I'm trying to do - depending on the entry chosed in the combo-box a number is assigned - this should then drive the 'Selection.AutoFilter Field:=' command. When I run this it basically hides everything, and it appears to be disregarding the column number as specified by "autono". So...not really working. Boo hoo.

Any ideas?

SamuelT

[stevekirk]

hi,

i am just as bad at vba

what does this do

Please Login or Register  to view this content.

steve

[stevekirk]

hi,

i modified the code to see if it worked
when i tested it -it did not work when i changed the cells to the "date" format it was ok

steve

[SamuelT]

Hi Steve,

Thanks for that - yes, the formatting to date worked (mine was on a custom format). So finally got everything working...gotta make a confession on this - I forgot there were a bunch of hidden columns hence the code was working but just on the hidden columns!

Anyhow - the final code looked like this:

Please Login or Register  to view this content.

Thanks for your input Steve - much appreciated.

SamuelT

[stevekirk]

nice to see it worked

this is the code i used

this allows you to add columns without adding it to the code

Please Login or Register  to view this content.

steve