Coding combobox to set autofilter parameters in worksheet

J

jls373

New Member
Joined
Apr 14, 2014
Messages
14
I am pretty new at VBA and am not sure why I am getting object error 424 Here is the code so far.
Code: 
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

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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 ().

Code: 
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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
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
 
Upvote 0
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?

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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:
Code: 
Select Case ComboBox1.Value And ComboBox2.Value

Here is the code in its entirety:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Replacing delete with a value
Replies
3
Views
209
Alex Blakenburg
A
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
661
Alex Blakenburg
A
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
586
Alex Blakenburg
A
B
  • Question
Autofilter using VBA code getting error with condition in different cell than table?
Replies
1
Views
67
dermie_72
D
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
162
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,637
Messages
6,125,965
Members
449,276
Latest member
surendra75

Share this page

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