Macro To Bring Up Filter Box for User Input

[rory121]

I have created a macro that allows me to manipulate a large data table into the format I need. Currently the date range it selects is fixed however for each report I run I may want to select a different date range.

How do I get the macro to bring up the filter box (like the example pictured below), allow the user to tick the desired dates and then let the rest of my macro continue? The filtered cell is always R1

Any help would be greatly appeaciated

 

[Tim_Excel_]

I had something similar I had to deal with, so I made a userform with a combobox in it. This lets the user click a filter and once the user clicks "ok" or whatever button you have put on the userform, the worksheet will filter for just that.

Private Sub UserForm_Initialize
With Workbooks([B]NAME[/B]).Worksheets([B]1[/B])
         ComboBox1.List = .Range(.Range("R2"), .Range("R2").End(xlDown)).value
end with
End Sub

Private Sub Button_filter_Click()
vlue = ComboBox1.value
Workbooks([B]NAME[/B]).Worksheets([B]1[/B]).Range("A:Z").AutoFilter Field:=18, Criteria1:=vlue, _
        Operator:=xlAnd
End Sub

I hope this works for you

 

[rory121]

Hi Tim_Excel_

Thanks for you help, I've created my userform and combobox with your code. When I run the macro, the drop down box is blank? It doesn't seem to be picking up my data in column R?

 

[Tim_Excel_]

Rory,

Seems like you need to set your references properly. The code works fine on my end.
-Did you just the workbooks name correctly? I used Workbooks("Book1").
-Same for the worksheet. Is it worksheet 1 you're using the filter on?
-Did you paste the code into the userform code?