Opening Built-In Custom AutoFilter Dialog Box Programmatically

  • Thread starter Thread starter nkjsterk
  • Start date Start date
N

nkjsterk

Hello,

I would like to know if it possible to open the built-In Custom
AutoFilter dialog box programmatically.

Manually I get it through selecting a filtered field, followed by
choosing "Custom..." in the pull down list. The automatic solution is
not in the help files as far as I know.

By the way,

Application.Dialogs(xlDialogFilter).Show

and

Application.Dialogs(xlDialogFilterAdvanced).Show

are not the one I exactly need. Is there a workaround?

Thanks in advance,

Nico Sterk
 
The menu you see when you select an autofiltered column is not one of the
Excel Dialogs, as you note. I don't know how to activate that menu
programatically, but you can set the autofilter criteria automatically
through the Range.AutoFilter method - see help for details. If you want to
make this interactive you may need to build your own custom dialog (userform)
for the user to enter the criteria.
 
Try this

Application.ExecuteExcel4Macro "FILTER?(1)"

where 1 is the number of column

or for a predefined value:

Application.ExecuteExcel4Macro "FILTER?(1, ""sometext"")"
 
Hi. For anyone else googling the answer to this, here it is for advanced filter dialog:
Application.Dialogs(xlDialogFilterAdvanced).Show

For autofilter, you can enable the filter on selected cells with
Application.Dialogs(xlDialogFilter).Show
this will give a 400 error if nothing valid is selected.
Keyboard shortcut Ctrl-shift-L could be used instead.

this applies to excel 365, probably other versions too.

Hope this helps
gazzat5
 
Back
Top