Coding combobox to set autofilter parameters in worksheet

[jls373]

I am pretty new at VBA and am not sure why I am getting object error 424 Here is the code so far.

Private Sub Workbook_Open()With Sheet1.ComboBox1
.AddItem ""
.AddItem "5% or less"
.AddItem ">5 - 15%"
.AddItem ">15% - 25%"
.AddItem ">25% - 35%"
.AddItem ">35%"
End With

With Sheet1.ComboBox2
.AddItem ""
.AddItem "-0.850 V or less negative"
.AddItem "more negative than -0.850 V"
End With
End Sub

Private Sub ComboBox1_Change()


If ComboBox1.Value = "" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=""
End If


If ComboBox.Value = "5% or less" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:="<=5"
End If


If ComboBox1.Value = ">5% - 15%" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">5", Criteria2:="<=15"
End If


If ComboBox1.Value = ">15% - 25%" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">15", Criteria2:="<=25"
End If


If ComboBox1.Value = ">25% - 35%" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">25", Criteria2:="<=35"
End If


If ComboBox1.Value = ">35%" Then
ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">35"
End If
End Sub


Private Sub ComboBox2_Change()


If ComboBox2.Value = "" Then
ActiveSheet.Range("g2:g3246").AutoFilter Field:=1, Criteria1:=""
End If


If ComboBox2.Value = "-0.850 V or less negative" Then
ActiveSheet.Range("g2:g3246").AutoFilter Field:=1, Criteria1:=">=-0.850"
End If


If ComboBox2.Value = "more negative than -0.850 V" Then
ActiveSheet.Range("g2:g3246").AutoFilter Field:=1, Criteria1:="<-0.850"
End If

End Sub

Any help with this would be greatly appreciated!

Thank you ahead of time,

Jeff

 

[jls373]

Update,

I thought the problem might be running through multiple if statements sequentially so I change code to select case, though now I get a new error "Variable not defined" where it highlights Private Sub ComboBox1_Change ().

Private Sub ComboBox1_Change()

Select Case Range("F2:F3246")
    Case ComboBox.Value = ""
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=""
    Case ComboBox.Value = "5% or less"
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:="<=5"
    Case ComboBox1.Value = ">5% - 15%"
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">5", Criteria2:="<=15"
    Case ComboBox1.Value = ">15% - 25%"
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">15", Criteria2:="<=25"
    Case ComboBox1.Value = ">25% - 35%"
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">25", Criteria2:="<=35"
    Case ComboBox1.Value = ">35%"
        ActiveSheet.Range("F2:F3246").AutoFilter Field:=1, Criteria1:=">35"
End Select


End Sub


Private Sub ComboBox2_Change()


Select Case Range("G2:G3246")
    Case ComboBox2.Value = ""
        ActiveSheet.Range("G2:G3246").AutoFilter Field:=1, Criteria1:=""
    Case ComboBox2.Value = "-0.850 V or less negative"
        ActiveSheet.Range("G2:G3246").AutoFilter Field:=1, Criteria1:=">=-0.850"
    Case ComboBox2.Value = "more negative than -0.850 V"
        ActiveSheet.Range("G2:G3246").AutoFilter Field:=1, Criteria1:="<-0.850"
End Select


End Sub

 

[dmt32]

Hi,
you are getting the error because you have this line "Case ComboBox.Value" you have omitted the 1 off the end of Combobox.

Not tested but see if this adjustment to your select case version helps:

Private Sub ComboBox1_Change()
    With ActiveSheet.Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        .AutoFilter
        Select Case ComboBox1.Value
        Case Is = ""
            'clear filter
        Case Is = "5% or less"
            .AutoFilter Field:=1, Criteria1:="<5", Operator:=xlAnd
        Case Is = ">5 - 15%"
            .AutoFilter Field:=1, Criteria1:=">5", _
                        Operator:=xlAnd, Criteria2:="<=15"
        Case Is = ">15% - 25%"
            .AutoFilter Field:=1, Criteria1:=">15", _
                        Operator:=xlAnd, _
                        Criteria2:="<=25"
        Case Is = ">25% - 35%"
            .AutoFilter Field:=1, Criteria1:=">25", _
                        Operator:=xlAnd, _
                        Criteria2:="<=35"
        Case Is = ">35%"
            .AutoFilter Field:=1, Criteria1:=">35", Operator:=xlAnd
        End Select
    End With
End Sub

Dave

 

[jls373]

I made the changes you suggested getting a new error "Compile Error: Variable not defined" and highlights the x1up.
Not sure what this does... I tried to get the definition from vba but it doesn't recognize it.
Thank you for your help this is the final assignment for my Chem Eng Comp class and the instructor doesn't know how to do it either.

Best regards,

Jeff

 

[jls373]

After I removed these and made some other minor adjustments the code is running great, now is there a way I can make it so that if I choose an options in both they work in conjunction with one another instead of running separate instances?

Private Sub ComboBox1_Change()    With ActiveSheet.Range("G1:G3246")
        .AutoFilter
        Select Case ComboBox1.Value
        Case Is = ""
            'clear filter
        Case Is = "5% or less"
            .AutoFilter Field:=1, Criteria1:="<=5"
        Case Is = ">5% - 15%"
            .AutoFilter Field:=1, Criteria1:=">5", _
            Criteria2:="<=15"
        Case Is = ">15% - 25%"
            .AutoFilter Field:=1, Criteria1:=">15", _
            Criteria2:="<=25"
        Case Is = ">25% - 35%"
            .AutoFilter Field:=1, Criteria1:=">25", _
            Criteria2:="<=35"
        Case Is = ">35%"
            .AutoFilter Field:=1, Criteria1:=">35"
        End Select
    End With
End Sub


Private Sub ComboBox2_Change()
    With ActiveSheet.Range("F1:F3246")
        .AutoFilter
        Select Case ComboBox2.Value
        Case Is = ""
            .AutoFilter Field:=1, Criteria1:=""
        Case Is = "-0.850 V or less negative"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = "more negative than -0.850 V"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        End Select
    End With
End Sub

Thanks ahead for all the fantastic help!

Jeff

 

[jls373]

An idea I had was inserting a command button that used the options in the comboboxes to create the select case options, but I also want to learn any other means.

 

[jls373]

My attempt at the commandbutton did not go over so well, received "run-time error 13: type mismatch" with the problem beginning with this line:

Select Case ComboBox1.Value And ComboBox2.Value

Here is the code in its entirety:

Option Explicit


Private Sub CommandButton1_Click()
    With ActiveSheet.Range("F1:G3246")
        .AutoFilter
        Select Case ComboBox1.Value And ComboBox2.Value
        Case Is = "" And ""
            'clear filter
        Case Is = "" And "-0.850 V or less negative"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = "" And "more negative than -0.850 V"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        Case Is = "5% or less" And ""
            .AutoFilter Field:=2, Criteria1:="<=5"
        Case Is = "5% or less" And "-0.850 V or less negative"
            .AutoFilter Field:=2, Criteria1:="<=5"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = "5% or less" And "more negative than -0.850 V"
            .AutoFilter Field:=2, Criteria1:="<=5"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        Case Is = ">5% - 15%" And ""
            .AutoFilter Field:=2, Criteria1:=">5", _
            Criteria2:="<=15"
        Case Is = ">5% - 15%" And "-0.850 V or less negative"
            .AutoFilter Field:=2, Criteria1:=">5", _
            Criteria2:="<=15"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = ">5% - 15%" And "more negative than -0.850 V"
            .AutoFilter Field:=2, Criteria1:=">5", _
            Criteria2:="<=15"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        Case Is = ">15% - 25%" And ""
            .AutoFilter Field:=2, Criteria1:=">15", _
            Criteria2:="<=25"
        Case Is = ">15% - 25%" And "-0.850 V or less negative"
            .AutoFilter Field:=2, Criteria1:=">15", _
            Criteria2:="<=25"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = ">15% - 25%" And "more negative than -0.850 V"
            .AutoFilter Field:=2, Criteria1:=">15", _
            Criteria2:="<=25"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        Case Is = ">25% - 35%" And ""
            .AutoFilter Field:=2, Criteria1:=">25", _
            Criteria2:="<=35"
        Case Is = ">25% - 35%" And "-0.850 V or less negative"
            .AutoFilter Field:=2, Criteria1:=">25", _
            Criteria2:="<=35"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = ">25% - 35%" And "more negative than -0.850 V"
            .AutoFilter Field:=2, Criteria1:=">25", _
            Criteria2:="<=35"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        Case Is = ">35%" And ""
            .AutoFilter Field:=2, Criteria1:=">35"
        Case Is = ">35%" And "-0.850 V or less negative"
            .AutoFilter Field:=2, Criteria1:=">35"
            .AutoFilter Field:=1, Criteria1:=">=-0.850"
        Case Is = ">35%" And "more negative than -0.850 V"
            .AutoFilter Field:=2, Criteria1:=">35"
            .AutoFilter Field:=1, Criteria1:="<-0.850"
        End Select
    End With
End Sub

Thank you for your time and knowledge.

Jeff

 

[jls373]

bump*