Consulting

Results 1 to 3 of 3

Thread: Attempting to extract 3 column listbox values after adding new row

  1. #1
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location

    Attempting to extract 3 column listbox values after adding new row

    Hello VBAExpress community,

    I'm working on a Calendar project and have hit a stumbling block.
    I have a userform with a listbox that gets populated from a sheet range when the userform initializes.
    On the form we can add items to the listbox.
    I am attempting to build an array of the new ListCount, but only get my original list.
    I can remove from the list and the removal is reflected, but additions are disregarded.
    If any one can see what I'm missing I would appreciate the illumination:

    Sub insertBills_UF()
    Dim a, b, r, c, x, I, J As Long
    Dim lbCnt As Integer
    Dim wsNC, wsB As Worksheet
    Dim aBills()
    
    Set wsNC = Sheets(Sheets.Count)
    Set wsB = Sheets("Bills")
    
    lbCnt = 0
    lbCnt = CalendarOpts.lbBills.ListCount - 1
    
    ReDim aBills(0 To lbCnt, 0 To 2)
    
        For a = 0 To lbCnt
            aBills(a, 0) = CalendarOpts.lbBills.List(a, 0)
            aBills(a, 1) = CalendarOpts.lbBills.List(a, 1)
            aBills(a, 2) = CalendarOpts.lbBills.List(a, 2)
        Next a
        
        With wsNC
            For b = 0 To lbCnt
                For r = 3 To 38 Step 6
                    For c = 1 To 7
                        If .Cells(r, c) = aBills(b, 2) Then
                            For x = 1 To 5
                                If .Cells(r + x, c).Value = "" Then
                                    .Cells(r + x, c).Value = aBills(b, 0) & "-" & aBills(b, 1)
                                    Exit For
                                End If
                            Next x
                        End If
                    Next c
                Next r
            Next b
        End With
    End Sub

  2. #2
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    BillCalendar_9.xlsm

    Here is my file as I have it so far.

    The idea is you can enter a bill name, amount, and day due.
    Then update the Month and year fields, then click make calendar.
    That works fine,
    But you can also click the Load Actions Menu button to further control what is on the calendar.

    CalendarOpts.jpg

    It all works rather well except the add bills button.
    Thank you for your attention.

  3. #3
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location

    userform controls options for a customizable bill scheduling calendar

    I got it working, I added a userform initialize call after the list box gets updated.
    I also added a button to send the list box values back to the worksheet.
    BillCalendar_9.xlsm
    Here are the results.
    Hope this helps people out there that want a way to print out a monthly calendar for the fridge to know what bills are coming up.
    The Actions menu gives a lot of tools to customize what goes on the calendar.

    -cheers
    mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •