Macro To Bring Up Filter Box for User Input

rory121

New Member
Joined
May 20, 2016
Messages
11
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

aid1508913-728px-Add-a-Filter-in-Excel-2007-Step-6.jpg



Any help would be greatly appeaciated
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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.

Code:
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
 
Last edited:

rory121

New Member
Joined
May 20, 2016
Messages
11
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_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,040
Messages
5,599,456
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top