Userform setting month range as filter

LNG2013

Active Member
Joined
May 23, 2011
Messages
466
I have a userform that allows users to select a client, then month, then goal, then objective. The code for each sets the autofilter to filter the results on a data sheet. I would like to change the month from a single month selection to a range. I am new to VBA and I am unsure how to do this... I assume I will need 2 month selectors one for the start and one for the end.

I have provided my current code below.

Forms - DataReport

Code:
Private Sub UserForm_Initialize()
Me.ComboBox2.Enabled = False
Me.ComboBox3.Enabled = False
Me.ComboBox4.Enabled = False
Me.RunButton.Enabled = False
End Sub

Private Sub ComboBox1_Change()
    DataReport.ComboBox2.Clear
    If Objective5m.FilterMode = True Then: Objective5m.ShowAllData
    Objective5m.Range("A1").AutoFilter field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
    Call FillCombobox(Objective5m.Range("X2", Objective5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
    
    Me.ComboBox2.Enabled = True
    
End Sub
Private Sub ComboBox2_Change()
    DataReport.ComboBox3.Clear
    Objective5m.Range("A1").AutoFilter field:=24, Criteria1:="=" & DataReport.ComboBox2.Value
    Call FillCombobox(Objective5m.Range("P2", Objective5m.Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox3)
    
    Me.ComboBox3.Enabled = True
End Sub
Private Sub ComboBox3_Change()
    DataReport.ComboBox4.Clear
    Objective5m.Range("A1").AutoFilter field:=16, Criteria1:="=" & DataReport.ComboBox3.Value
    Call FillCombobox(Objective5m.Range("Q2", Objective5m.Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox4)
    
    
    Me.ComboBox4.Enabled = True
    Me.RunButton.Enabled = True
End Sub
Private Sub ComboBox4_Change()
    Objective5m.Range("A1").AutoFilter field:=17, Criteria1:="=" & DataReport.ComboBox4.Value
End Sub
Private Sub RunButton_Click()
Unload DataReport
Call Filtered
Call AMasterBuild
End Sub
Private Sub CancelButton_Click()
Unload DataReport
End Sub


Microsoft Excel Objects: This Workbook
Code:
Private Sub Workbook_Open()
 
DataReport.ComboBox1.Clear
    Columns("N:N").Select
    Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Objective5m.Range("A1").AutoFilter
Call FillCombobox(Objective5m.Range("N2", Objective5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
DataReport.Show
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You would need to individual Combo box to do this effectively.

Name one as cboStartMonth and the other cboEndMonth then use them as the point of the filters.
 
Upvote 0
Hey Trevor,
Yeah I have now adjusted the userform to add another ComboBox (2 now for months). But I am unsure how to use them to set a range for the months. When I set the first month range, and then attempted the second it came back with a blank combobox.

My months are all located in column X.
 
Upvote 0
Test this out based on the combobox names, adjust the names and range

Private Sub cmdOK_Click()
Selection.AutoFilter
ActiveSheet.Range("$F$1:$F$20").AutoFilter Field:=1, Criteria1:=Me.cboStartMonth, _
Operator:=xlOr, Criteria2:=Me.cboEndMonth
End Sub
 
Upvote 0
Hey Trevor so I tried your method my code is below.
I keep getting an error of 1004 - Autofilter method of Range Class failed

Private Sub UserForm_Initialize()
Me.ComboBox2.Enabled = False
Me.ComboBox3.Enabled = False
Me.ComboBox4.Enabled = False
Me.ComboBox5.Enabled = True
Me.RunButton.Enabled = False
End Sub

Private Sub ComboBox1_Change()
DataReport.ComboBox2.Clear
If Objective5m.FilterMode = True Then: Objective5m.ShowAllData
Objective5m.Range("A1").AutoFilter Field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
Call FillCombobox(Objective5m.Range("X2", Objective5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
Call FillCombobox(Objective5m.Range("X2", Objective5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox5)
Me.ComboBox2.Enabled = True
Me.ComboBox5.Enabled = True

End Sub
Private Sub ComboBox2_Change()
DataReport.ComboBox3.Clear
Objective5m.Range("A1").AutoFilter Field:=24, Criteria1:="=" & DataReport.ComboBox2.Value
Call FillCombobox(Objective5m.Range("P2", Objective5m.Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox3)

Me.ComboBox3.Enabled = True

End Sub
Private Sub ComboBox5_Change()
End Sub
Private Sub ComboBox3_Change()
DataReport.ComboBox4.Clear
Objective5m.Range("A1").AutoFilter Field:=16, Criteria1:="=" & DataReport.ComboBox3.Value
Call FillCombobox(Objective5m.Range("Q2", Objective5m.Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox4)

Me.ComboBox4.Enabled = True
Me.RunButton.Enabled = True
End Sub
Private Sub ComboBox4_Change()
Objective5m.Range("A1").AutoFilter Field:=17, Criteria1:="=" & DataReport.ComboBox4.Value
End Sub
Private Sub RunButton_Click()
Unload DataReport
Selection.AutoFilter
'ActiveSheet.Range("$N$1:$N$20").AutoFilter Field:=14, Criteria1:=Me.ComboBox1
ActiveSheet.Range("$X$1:$X$20").AutoFilter Field:=24, Criteria1:=Me.ComboBox2, _
Operator:=xlOr, Criteria2:=Me.ComboBox5
Call Filtered
Call AMasterBuild
End Sub
Private Sub CancelButton_Click()
Unload DataReport
End Sub
 
Upvote 0
I also noticed it seems to reset my other autoilters when this one take affect. So the first combobox filters the Clients, and when I do run the code below it removes that filter.
 
Upvote 0
Take out the Selection.AutoFilter.

Try this one line out, I have tested it

Rows("1").AutoFilter Field:=6, Criteria1:=Me.cboStartMonth, Operator:=xlOr, Criteria2:=Me.cboEndMonth
 
Upvote 0
Dude!!! You rock!!! That worked!!! Awesome!

Do you know of any way to sort what is returned to the ComboBox so they are in order? My boxes currently give me the values as it has encountered them in the column. Example I might get 10, 9, 6, 8, 7 for my months.
 
Upvote 0
Can you show something from the sheet as an example.

Simple copy and paste into the thread will do.
 
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,382
Members
452,322
Latest member
CrimsonCoure

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
Back
Top