Excel 2010, VBA, UserForm, Filter by first/last half of month.

[BobLaf]

I'm trying to filter a table by the first/last half of the month using a userform to select the range. My userform has a combobox to select the month and then a set of optionbuttons to pick either the first or last half of the month (1-15/16-31). Currently I cant even get it to filter by month using my existing code.

Private Sub FilterButton_Click()
     
    Dim MyInput As Date
    Dim MyInput2 As Date
    Dim Month As Date
    
    If DateFilter.OptionButton1.Value = True Then
        Day1 = "1"
        Day2 = "15"
    Else
        Day1 = "16"
        Day2 = "31"
    End If
 
    MyInput = Month("Month")
    MyInput2 = Month("Month")
    MyInput3 = JobBox
     
    Worksheets("Timesheet").Range("Table_Joined_Query").AutoFilter Field:=1, Criteria1:=">=" & MyInput, Operator:=xlAnd, Criteria2:="<=" & MyInput2
    If MyInput3 <> "" Then Worksheets("Timesheet").Range("Table_Joined_Query").AutoFilter Field:=2, Criteria1:=MyInput3 Else
    Worksheets("Timesheet").Range("Table_Joined_Query").Sort Key1:=Range("Table1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Unload Me
End Sub

 

[Worf]

Hello
I made a few assumptions:

- The source range to populate the combo box contains dates
- The range to be filtered is a real table with headers
- The upper left corner of the table is cell M1
- Column P is not part of the table

If you have trouble adjusting this example to your actual project, let me know…

Private Sub FilterButton_Click()
Dim ast$


If OptionButton1.Value = False And OptionButton2.Value = False Then
    MsgBox "Select a month period", vbCritical
    Exit Sub
End If


ast = ">15"
If OptionButton1.Value Then ast = "<=15"        ' OpButton1 = first half of month


' filter criteria is a formula at P2
Range("p1").Value = ""
Range("p2").Formula = "=and(month(m2)=" & CStr(Month(ComboBox1.Value)) & ",day(m2)" & ast & ")"
Range("m2").ListObject.Range.AdvancedFilter xlFilterInPlace, _
criteriarange:=Range("p1:p2"), unique:=False


End Sub

 

[BobLaf]

Thanks for this, I ended up just using some cells on the worksheet and a macro button to filter.