Custom auto filter code

[touchstone57]

Hi there.

I just wander what the VBA code is to open a custom auto filter box in excel is?

I'm creating a dynamic worksheet, and when you click a command button you have a list of filters to choose from, and I wandered what the code is that opens a custom auto filter box and allow the user to make their selection.

[Sidewinder]

'm creating a dynamic worksheet, and when you click a command button you have a list of filters to choose from

Not really sure what your asking (some days I'm dumb as a brick ), but you can check if worksheets("name").autofiltermode is on and then use the .filters collection to cycle through the filters and place each one in a control for user selection.

 

[touchstone57]

'm creating a dynamic worksheet, and when you click a command button you have a list of filters to choose from

Not really sure what your asking (some days I'm dumb as a brick ), but you can check if worksheets("name").autofiltermode is on and then use the .filters collection to cycle through the filters and place each one in a control for user selection.

 

I tried this but It didn't work.....

This is what I want. This is the code that filters the selected cells

Selection.AutoFilter Field:=1, Criteria1:="Name"

When you click the command button, it executes this code to filter the criteria you selected (from a drop down box).

I have all the codes working for the different criteria, apart from the Custom auto filter box. I just wandered what code, would allow you to click the command button and open an custom auto filter box.

Here is a picture just in case....



In an attempt to find this code, I found Selection.AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd but this does not allow the user to input their own selections.

[Sidewinder]

Thank you for clearing that up. At first I thought this would be simple enough to use the Application.Dialogs(xlDialog).Show method and then discovered there is no dialog for the Custom AutoFilter.

Only other thing I can think of it to show the xlDialogFilter. If you can get a handle on the collection of selections, you'll notice the Custom AutoFilter is always third on the list. (index = 2)

 

[touchstone57]

What exactly do I have to do then? put what code under my command button?

[Sidewinder]

I was unable to turn up anything from the Net, nor was Microsoft very helpful. Even my world famous snippet closet (IMHO) came up empty

I guess in this case custom means do it yourself. Usually I find by doing something manually and recording a macro, that it produces the basis for more efficient self-written VBA code, but in this case the macro was empty except for some boilerplate comments.

My suggestion would be to design a custom dialog and grab the parameters you need to setup the Selection.AutoFilter statement. Or you could just let the user choose custom from the autofilter list and fill in the dialog.

Good luck. 

PS. For what it's worth, I did find there is a xlTop10Items constant.

[touchstone57]

Well you see the thing is, (because of my assignment guidelines) the user sis not allowed to select anything from the cells/filter list, so it has to be done by other methods 

And I don't know what this means "My suggestion would be to design a custom dialog and grab the parameters"?

One way I could do it is, have an input box appear and using a lot of variables and strings, adding them up and getting some sort of custom filter (perhaps difficult and a lot of bother)

[Sidewinder]

I guess if it were me, I'd design a form to duplicate the Custom AutoFilter dialog; show the form when the command button is clicked and use the data entered by the user to build the criteria1 and operator parameters of the Selection.AutoFilter instruction.

If you record and edit a few macros using the built in Custom AutoFilter dialog, you'll see the relationships between the data entered in the dialog and where they end up on the selection statement.

Both the VBA and the Excel help are great sources of information. As mentioned I was unable to find any information doing this with built in properties or methods. I'm currently using Excel 2002, so if you have a more recent model perhaps Microsoft addressed this issue.

 

When you say assignment, are you referring to homework

[touchstone57]

I guess if it were me, I'd design a form to duplicate the Custom AutoFilter dialog; show the form when the command button is clicked and use the data entered by the user to build the criteria1 and operator parameters of the Selection.AutoFilter instruction.

If you record and edit a few macros using the built in Custom AutoFilter dialog, you'll see the relationships between the data entered in the dialog and where they end up on the selection statement.

Both the VBA and the Excel help are great sources of information. As mentioned I was unable to find any information doing this with built in properties or methods. I'm currently using Excel 2002, so if you have a more recent model perhaps Microsoft addressed this issue.

 

I will now.

That is what I have been doing with most of them, creating macros then using autofilter options, than adapting it for my worksheet. Its just that recording the custom auto filter selection showed the code to be a bit more complicated, and I wanted to find the easiest way   

When you say assignment, are you referring to homework

Yes I am  but don't worry I am well within limits. My assignment guidlines say I have to create the worksheets, not what happens in them, this is merely fixing an error. After all, I have done all of it myself, I can understand and apply it just this particular code eludes because excel does not give info on it....

Basically, here is a little inside into my code (if it helps)

Basically, you select the filter option you want from the drop down box. Then click the command button which executes the code.



Private sub [......]

    Range("B3:B15").Select

    If combobox = "Make" Then
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="Make"
    End If
 
End sub

And here is the code I got from the Macros of Custom auto filter

ActiveWindow.SmallScroll Down:=-9
    Selection.AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd

But it is far to long for me to allow the user to do manually.