I'm new to VBA and I'm trying to set up a customizable sheet that allows the user to filter certain columns based on the checkboxes that I have set up. So far, I understand how checkboxes work and how I can integrate them into the code, but I think I have an issue with the autofilter function. Specifically, I think that I'm putting the wrong value for Criteria1.
I've been looking around for similar coding problems, but none of them seem to work with what I'm trying to do.
Sub Auto_filter()
'variables are for checkboxes'
Dim VC1500 As Shape
Dim VC7500 As Shape
Dim VC144024 As Shape
'initiates to check for the checkboxes'
Set VC1500 = Sheets("Sheet7").Shapes("Check Box 4")
Set VC7500 = Sheets("Sheet7").Shapes("Check Box 5")
Set VC144024 = Sheets("Sheet7").Shapes("Check Box 6")
'if statement that will add a string to strCriteria if checkbox is true'
If VC1500.OLEFormat.Object.Value = 1 Then
strCriteria = strCriteria & ", VC1500"
End If
If VC7500.OLEFormat.Object.Value = 1 Then
strCriteria = strCriteria & ", VC7500"
End If
If VC144024.OLEFormat.Object.Value = 1 Then
strCriteria = strCriteria & ", 144024"
End If
'with statement that finds for column vendor then filter it based on
strCriteria, I think this is where my issue is'
With Worksheets("Open Purchase Orders")
With .Range("A1", .Cells(1, Columns.Count).End(xlToLeft))
Set vendorfind = .Rows(1).Find("Vendor")
If Not vendorfind Is Nothing Then
.AutoFilter Field:=vendorfind.Column,
Criteria1:=Split(strCriteria, ", "), Operator:=xlFilterValues
End If
End With
.AutoFilterMode = False
End With
End Sub
I expect to have the sheet filtered based on the checkboxes. I get a runtime error 9 error:subscript out of range
Aucun commentaire:
Enregistrer un commentaire