jeudi 27 juin 2019

VBA: How to filter through a sheet based on a checkbox?

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