Announcement

Collapse
No announcement yet.

Failed listbox entries

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.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, 8)
    End If

    Next k

    End Sub


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

  • #2
    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

    Comment


    • #3


      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!

      Comment

      Working...
      X