UserForm moving Multiple Column rows between multiple List Boxes

[troyaaron]

Hi All,

So I've been stuck on this for a long time and will hugely appreciate any help given.

Basically:

I have a Userform with 6 List Boxes on it.
Let's call them:
Group1
Group2
Group3
Group4
Group5 and
TempGroup

Each Group (1-5) is populated with a 2 column list from range on the spreadsheet.
TempGroup is empty to start and will act as a holding box.

I have a button next to each Group(1-5) and then 5 x numbered buttons next to the TempGroup listbox.

The idea is this...

The user can select any item in a Group listbox, and click the button next to the lsitbox which will transfer the 2 column row into the TempGroup listbox.
They can then click the appropriate numbered button, eg. Button 2, and transfer that 2 column row back into Group 2 listbox.

What's more.....(sorry)
The first column of data is a product name, the second column is a price.
It would be good if each list box could have a label that keeps subtotal of the prices of items currently in that Group list box (changes as items are moved between Groups via the TempGroup).
Once the user is happy with Group setup a button will allow that configuration of lists to be put into a spreadsheet.

I did contemplate drag and drop but that killed my brain so would settle for the button approach as described but cant even get that working....

 

[mikerickson]

Something like this.

Private Sub butGroup1ToTemp_Click()
    MoveSelectedListItems fromListBox:=Me.Group1, toListBox:=Me.GroupTemp
End Sub
Private Sub butGroup2ToTemp_Click()
    MoveSelectedListItems fromListBox:=Me.Group2, toListBox:=Me.GroupTemp
End Sub

Private Sub butTempTo1_Click()
    MoveAllListItems fromListBox:=Me.GroupTemp, toListBox:=Me.Group1
End Sub
Private Sub butTempTo2_Click()
    MoveAllListItems fromListBox:=Me.GroupTemp, toListBox:=Me.Group2
End Sub

Sub MoveSelectedListItems(fromListBox As MSForms.ListBox, toListBox As MSForms.ListBox)
    Dim i As Long
    With fromListBox
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) Then
                toListBox.AddItem .List(i, 0)
                toListBox.List(toListBox.ListCount - 1, 1) = .List(i, 1)
                .RemoveItem i
            End If
        Next i
    End With
    
    LBTotalToLabel fromListBox
    LBTotalToLabel toListBox
End Sub

Sub MoveAllListItems(fromListBox As MSForms.ListBox, toListBox As MSForms.ListBox)
    Dim i As Long
    With fromListBox
        For i = .ListCount - 1 To 0 Step -1
            toListBox.AddItem .List(i, 0)
            toListBox.List(toListBox.ListCount - 1, 1) = .List(i, 1)
            .RemoveItem i
        Next i
    End With
    
    LBTotalToLabel fromListBox
    LBTotalToLabel toListBox
End Sub

Sub LBTotalToLabel(aListBox As MSForms.ListBox)
    Me.Controls(Replace(aListBox.Name, "Group", "Label")).Caption = SumOfListBox(aListBox)
End Sub
Function SumOfListBox(aListBox As MSForms.ListBox) As String
    Dim i As Long
    With aListBox
        For i = 0 To .ListCount - 1
            SumOfListBox = CStr(Val(SumOfListBox) + Val(.List(i, 1)))
        Next i
    End With
End Function

 

[troyaaron]

THanks for reply...

I get an error here....

Me.Controls(Replace(aListBox.Name, "Group", "Label")).Caption = SumOfListBox(aListBox)

But as you can see I haven't changed it...not sure what this all means

Should a part of this be changed to a label name?

 

[troyaaron]

THanks for reply...

I get an error here....

Me.Controls(Replace(aListBox.Name, "Group", "Label")).Caption = SumOfListBox(aListBox)

But as you can see I haven't changed it...not sure what this all means

Should a part of this be changed to a label name?

It says Could not find the specified object

 

[mikerickson]

That code assumes that for each of the Group1, Group2, ..., GroupTemp list boxes, there is a corresponding Label1, Label2,..., LabelTemp to hold the sum column 2 entries.

Either change the name of your controls or change the code to reflect your naming conventions.

 

[troyaaron]

I added a On Error Resume Next before that line and it now works
Not sure if this is good practice

I also changed the MoveAllListItems to MoveSelectedListItems (I dont want user to move all ever)

However, it still moves all items (but not everytime it seems)

any ideas?

 

[troyaaron]

It seems to be....if I select the bottom row of list, then it will move all even with the MoveSelectedListItems

 

[mikerickson]

It worked for me. But I did have .MultiSelect set for the list boxes Group1, Group2,...Group5.

I misinterpreted the OP, I thought that you had (five) buttons, each of which moved all the entries from GroupTemp to GroupN.

 

[troyaaron]

I changed them to MultiSelect and it all seems to work fine now...

One more thing would be great....I know you've helped me so much already.


The Label only displays the total once i have moved an entry from that list...can i get it to show the subtotals on initialise?

 

[mikerickson]

Put

LBTotalToLabel Me.Group1
LBTotalToLabel Me.Group2
'...
LBTotalToLabel Me.Group5
LbTotalToLabel Me.GroupTemp

at the end of your Intialize event.