Failed listbox entries

  • So up until now I've managed to piece together solutions from questions others have asked. I am very new to VBA but to date I have always been able to figure it out... until now. I'm really hoping someone here can give me some guidance. I tried copying a range into a listbox but I could only get 1 match to show. I then tried to individually fill each matching cell to each listbox item. Unsuccessful at both.

    What I'm trying to do:

    I have a 7 column non-varying data source for personal expenses. I'm trying to match 2 criteria 1. mnth = December and 2. if there's any non blank item in column 7 or "G".
    If those 2 criteria match, I want that row to be inserted into listbox1. Here is what I have attempted and doesn't work:

    Private Sub UserForm_Initialize()

    Dim ws1 As Worksheet
    Dim k As Integer
    Dim lstrow As Long

    Set ws1 = ThisWorkbook.Sheets("Expenses")
    lstrow = ws1.Cells(ws1.Rows.Count, 8).End(xlUp).Row
    mnth = "December"

    With Me.ListBox1
    ListBox1.ColumnCount = 7

    End With

    For k = 2 To lstrow
    If ws1.Cells(k, 8).Value = mnth And ws1.Cells(k, 7).Value <> vbNullString Then
    ListBox1.AddItem ws1.Cells(k, 2).Value 'Sheets("expenses").Range("B" & k, "H" & k).Value
    ListBox1.List(k - 2, 1) = ws1.Cells(k, 3)
    ListBox1.List(k - 2, 2) = ws1.Cells(k, 4)
    ListBox1.List(k - 2, 3) = ws1.Cells(k, 5)
    ListBox1.List(k - 2, 4) = ws1.Cells(k, 6)
    ListBox1.List(k - 2, 5) = ws1.Cells(k, 7)
    ListBox1.List(k - 2, 6) = ws1.Cells(k, 8)
    End If

    Next k

    End Sub

    I appreciate anyone's help with this. Thanks in advance!

  • when you add an item, you must access it by it's listindex
    when you add an item to the end of a list, you can use listcount - 1 to get the item just added

    there are other ways to do this, (you could filter your spreadsheet by your month and null then range add it to your listbox as well.)

    also, I made an error in testing, you had "December" for the month, but I put december in spread sheet and it did not find it.
    (I forgot about case sensitivity)
    if you want to avoid case sensitivity, in your if statement you could add
    If LCase(ws1.(Cells(k, 8))) = mnth And ws1.Cells(k, 7) <> vbNullString Then

    and change mnth from December to december

  • Ugh I was so close! lol. Thank you so much for your help! Works great!

    I didn't care about case sensitivity because the value "December" will actually come from a combobox dropdown that will always have the proper caps but appreciate the additional tip!

    I actually was originally range adding it but then I went to individually adding when it wasn't working. I might go back now that I see where I went wrong.

    Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!