Failed listbox entries

Xysumn · Feb 5th 2019

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:

[VBA]
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.Clear
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,
End If

Next k

End Sub
[/VBA]

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

rabsofty · Feb 5th 2019

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

Code

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"


    ListBox1.Clear
    ListBox1.ColumnCount = 7
   
    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
        li = ListBox1.ListCount - 1 'get listindex of item just added
        ListBox1.List(li, 1) = ws1.Cells(k, 3)
        ListBox1.List(li, 2) = ws1.Cells(k, 4)
        ListBox1.List(li, 3) = ws1.Cells(k, 5)
        ListBox1.List(li, 4) = ws1.Cells(k, 6)
        ListBox1.List(li, 5) = ws1.Cells(k, 7)
        ListBox1.List(li, 6) = ws1.Cells(k, 8)
      End If
    Next k

Display More

Xysumn · Feb 8th 2019

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!

Failed listbox entries

Participate now!

Share

Users Viewing This Thread