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
 

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

Back
Top