Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Problem manipulating listbox list

    I am struggling with manipulating data in my two listboxes on my userform.

    My userform consists of two listboxes.

    Listbox1 holds a dynamic list of dates (records) represented by named range "data_file_list" :
    Code:
    (Workbooks("Sports15b.xlsm").Names.Add Name:="data_file_list", RefersTo:="=OFFSET('[" & wb1.Name & "]" & temp_ws.Name & "'!$B$1,1,0,count('[" & wb1.Name & "]" & temp_ws.Name & "'!$A:$A),2)"
    The user then selects any combination, or all, of the records they wish to have further processed. Each record selected is processed individually through the use of a loop function.

    Following the completion of that individual record's process, I am trying to have that entry removed from listbox1 (completed, no longer an option for selection), and added to listbox2 (records completed list).

    This is what I have so far ...

    Code:
    Sub commandbutton4_click()
    
        Dim rnglb2 as range
    Set rnglb2 = Range(wsth.Range("A2"), wsth.Range("A" & Rows.Count).End(xlUp))
        For x = 1 to numrows                'numrows = 3 ie all 3 items in the listbox are selected for processing
    
       ' Some record prsocessing code
    
            update_listbox2 (ByVal rnglb2 As Range)
    
        's ome more record processing code
    
        Next x
    
    End Sub


    Code:
    Sub update_listbox2(ByVal rnglb2 As Range)
    ' moves processed date from listbox1 (selection) to listbox2 (completed)
             
        Dim I As Double
             
        'populate userform listbox2 with recently processed record
        With UserForm1.ListBox2
            If .ListCount = 0 Then .Height = 15
            If Not IsEmpty(rnglb2(.ListCount + 1)) Then
                .AddItem rnglb2(.ListCount + 1)
                .Height = .Height + 15
                .Parent.Height = .Parent.Height + 15
            End If
        End With
        
        'remove selected items from listbox1 (bottom up)
        With UserForm1.ListBox1
            For I = .ListCount - 1 To 0 Step -1
                If .List(I) = rnglb2(.ListCount + 1).Value Then
                    .RemoveItem I
                End If
            Next I
        End With
    
    
    End Sub

    rnglb2 is a range of cells identical to named range "data_file_list" used to create the list. Just a different source worksheet.

    My code to remove the item from listbox1 does not work. Is anyone able to suggest how I can accomplish what I need to do without a whole lot of project restructuring?

    Jenn

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jenn,

    I think your problem is that you are passing rnglb2 byVal (value), which means you're working on a copy. You should pass it byRef (reference) which means you're working with the original range. It actually works a little different in VBA than other languages but that is the gist of it.

    Here's a MS Article on the subject.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you sir for your suggestion.

    No errors, listbox2 is having the the value added, but listbox1 isn't losing the value.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How exactly do you populate listbox1 in the first place?

    @RG,

    For objects, ByVal does not pass a copy of the object, it passes a copy of the pointer to the object.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2016-03-21)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jenn,

    Thanks to Rory's post I investigated further and it seems this is a syntactical problem

    Try this:
    Code:
       With UserForm1.ListBox1
            For I = .ListCount - 1 To 0 Step -1
                If .List(I) = rnglb2(.ListCount + 1).Value Then
                    .RemoveItem(I)
                End If
            Next I
        End With
    HTH
    [/code]
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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