Use the custom filter dialog in a macro

K

KenInPortland

I need some guidance. I can use the recorder but don't understand how to
initiate and put the parameters into an existing Excel dialog. I hope this is
doable.

I want to initiate the custom filter dialog box using a macro with the
column fixed (say column E) beginning at row 33 with parameters set to
"contain" then allow a user to enter the two search words and perform the
filter.

Then I need another macro to essentially do a "filter all" and display all
the rows.

Thanks,
 
H

Héctor Miguel

hi, Ken !
I need some guidance.
I can use the recorder but don't understand how to initiate and put the parameters into an existing Excel dialog.
I hope this is doable.
I want to initiate the custom filter dialog box using a macro with the column fixed (say column E)
beginning at row 33 with parameters set to "contain" then allow a user to enter the two search words and perform the filter.
Then I need another macro to essentially do a "filter all" and display all the rows...
Click to expand...

use the "ancient" xl4 macro-functions to "ask/show" *that* dialog to the user...
previously "send" a keystroke sequence to the parameters "preparation"...
and let the user to finish the job (i.e.)

assuming column E is the 5th filtered field in your layout...

Sub Show_CustomFiterDialog()
SendKeys "+{tab}{down}{end}{up}{tab}"
ExecuteExcel4Macro "filter?(5)"
End Sub

there are several ways to "show all" and/or quit the autofilter mode

regards,
hector.
 
K

KenInPortland

Thank you Hector. Works great! I appreciate it.

Héctor Miguel said:
hi, Ken !


use the "ancient" xl4 macro-functions to "ask/show" *that* dialog to the user...
previously "send" a keystroke sequence to the parameters "preparation"...
and let the user to finish the job (i.e.)

assuming column E is the 5th filtered field in your layout...

Sub Show_CustomFiterDialog()
SendKeys "+{tab}{down}{end}{up}{tab}"
ExecuteExcel4Macro "filter?(5)"
End Sub

there are several ways to "show all" and/or quit the autofilter mode

regards,
hector.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to Filter on variable column 5
Macro - filter - show all data 2
Filter by Top 10 doesn't work 1
AutoFilter: custom filter hides all 4
Custom auto filter (Dates) 2
macro custom filter 0
Macro to copy value in field and insert it into filter 2
adding a control button with a filter 2

Top