Removing listbox items based on the values in 2 columns - vba

Question

I am relatively new to VBA and am working on creating some forms to help with inventory management.

When the form is initialized, there is a listbox that will pull product information from an inventory sheet. Each row has 11 columns with information like product ID, vendor, price, items in stock, etc. There are also three checkboxes - Items Below Par, Items At Par, and Items Above Par. The 3 checkbox values are set to True to begin because all Inventory is being displayed in the listbox when the form is initialized.

I am trying to write code that will remove products from the listbox when one of the checkboxes are unchecked. For example, if I uncheck "Items Below Par" I want all products where the # of items in stock is < the par value for that item (which is another column) to be removed. This would leave the listbox only displaying those items that are at or above par.

How would I go about doing this? I am not sure how to reference the values in the listbox columns.

Thank you in advance!! Please ask questions if this is unclear.

---

Private Sub UserForm_Initialize()

Dim lr As Long
Dim Inv As Worksheet
Dim rng As Range

Set Inv = Sheets("Inventory")
lr = Inv.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Inv.Range("A2:K" & lr)

Me.lbInventory.ColumnCount = 11
Me.lbInventory.RowSource = rng.Address
Me.lbInventory.ColumnHeads = True

Me.chkAbove.Value = True
Me.chkBelow.Value = True
Me.chkAt.Value = True

End Sub


Private Sub chkAbove_Change()
    ListBuild
End Sub

Private Sub chkAt_Change()
    ListBuild
End Sub

Private Sub chkBelow_Change()
    ListBuild
End Sub


Sub ListBuild()

Dim Inv As Worksheet
Set Inv = Sheets("Inventory")

Dim r As Integer
Me.lbInventory.Clear

If Me.chkBelow.Value = True Then
    For r = 1 To 11
        If Inv.Cells(r, 7).Value <
           Inv.Cells(r, 9).Value Then
                Me.lbInventory.AddItem Inv.Cells(r, 1).Value
        End If
    Next r
End If

If Me.chkAt.Value = True Then
    For r = 1 To 11
        If Inv.Cells(r, 7).Value =
           Inv.Cells(r, 9).Value Then
                Me.lbInventory.AddItem Inv.Cells(r, 1).Value
        End If
    Next r
End If

If Me.chkAbove.Value = True Then
    For r = 1 To 11
        If Inv.Cells(r, 7).Value >
           Inv.Cells(r, 9).Value Then
                Me.lbInventory.AddItem Inv.Cells(r, 1).Value
        End If
    Next r
End If

End Sub

I am getting a Compile error. Expected expression for these 3 statements:

If Inv.Cells(r, 7).Value >,<,=
        Inv.Cells(r, 9).Value Then

Answer 1

There may be a cleaner way and I encourage anyone who has a cleaner way to join in, but this seems to work. Basically, rebuild the listbox list under the change event of each check box. Below I have an example assuming the checkboxes are named BelowPar, AtPar, & AbovePar.

Private Sub AbovePar_Change()
    ListBuild
End Sub

Private Sub AtPar_Change()
    ListBuild
End Sub

Private Sub BelowPar_Change()
    ListBuild
End Sub

Sub ListBuild()

    Dim r As Integer
    InventoryList.Clear

    If BelowPar.Value = True Then
        For r = 1 To 11
            If Sheets("InventorySheet").Cells(r, 2).Value < _
               Sheets("InventorySheet").Cells(r, 3).Value Then
                    InventoryList.AddItem Sheets("InventorySheet").Cells(r, 1).Value
            End If
        Next r
    End If

    If AtPar.Value = True Then
        For r = 1 To 11
            If Sheets("InventorySheet").Cells(r, 2).Value = _
               Sheets("InventorySheet").Cells(r, 3).Value Then
                    InventoryList.AddItem Sheets("InventorySheet").Cells(r, 1).Value
            End If
        Next r
    End If

    If AbovePar.Value = True Then
        For r = 1 To 11
            If Sheets("InventorySheet").Cells(r, 2).Value > _
               Sheets("InventorySheet").Cells(r, 3).Value Then
                    InventoryList.AddItem Sheets("InventorySheet").Cells(r, 1).Value
            End If
        Next r
    End If

End Sub

Adjust ranges and sheet name to suit your needs. For this example Cells(r, 1).Value = Item name, Cells(r, 2).Value = # of items in stock, and Cells(r, 3).Value = Par Value. Where 1, 2, 3 are the column number containing the data.