Creating a group for items in a listbox

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Is there a way to create groups of line items from the listbox?

I have a userform that allows me to show and hide certain columns. There are 2 listboxes in the userform; one to 'Show' the list item and the other to 'Hide' the list item. By double clicking the items in the listboxes, a user can move the items between lists. Here my idee:

I place a button called "Create Group" on the my userform. The "Create Group" button opens a new userform. The "Create Group" userform is similar to first userform, it has 2 listboxes. The first listbox will have all the list items. When the user double clicks a list item, the list item goes into the second listbox which becomes the group. The user can add as many list items to the second listbox for grouping. When they are finished, they enter the name of the grouping in a field box and click a button called "Finish". When they click "Finish", the newly created group name will appear in the first userform listbox. When a user double clicks the group name, all of the competitors associated with the group move to the opposite listbox which will allow the users to hide and show columns for groups of list items in one action.

Ideas?
 
I cannot understand why you think you need two UserForms.

I'm not really able to help on this question if you for some reason think you need two UserForms.

I have built hundreds of UserForms and written code to do a lot of jobs and never needed two UserForms to do a particular task.

I believe I asked this question earlier but you never explained.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sorry. I only have 1 userform but the one I have is called ("UserForm2"). I had made an earlier userform during testing.

I have the listboxes ready to go on my userform
 
Upvote 0
I was able to get the Group tables loaded into ListboxGroupsVisible as list items. Now I need to figure out how to get it so that when a user double-clicks the Group name list item, all the competitors in that group table hide. When a user doubles-clicks ("Group1") in ListboxGroupsVisible, the list item ("Group1") should move into the other listbox , ("ListboxGroupsHidden") and visa vera. Here is my code:
Code:
Private Sub ToggleVisible_Click()Columns(4).Resize(, 37).Hidden = Not Columns(4).Resize(, 37).Hidden
UpDate_List
End Sub
Private Sub ListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListboxHidden.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = False
        
For i = 0 To ListboxHidden.ListCount - 1
    If ListboxHidden.Selected(i) Then
        ListboxHidden.RemoveItem (i)
    End If
Next i
ListboxHidden.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub
Private Sub ListboxVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListboxVisible.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = True
        
For i = 0 To ListboxVisible.ListCount - 1
    If ListboxVisible.Selected(i) Then
        ListboxVisible.RemoveItem (i)
    End If
Next i
ListboxVisible.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub


Private Sub UserForm_Activate()
ListboxHidden.Clear
ListboxVisible.Clear
For i = 4 To 40
    If Columns(i).Hidden = True And Cells(7, i) <> "" Then ListboxHidden.AddItem Cells(7, i).Value
    If Columns(i).Hidden = False And Cells(7, i) <> "" Then ListboxVisible.AddItem Cells(7, i).Value
Next
ListBoxGroupsHidden.Clear
ListBoxGroupsVisible.Clear


Dim T As ListObject
With Sheets("Competitor Comparison")
UserForm2.ListBoxGroupsVisible.Clear
    For Each T In Sheets("Groups").ListObjects
        ListBoxGroupsVisible.AddItem T.Name
    
    Next
End With
ListboxHidden.ControlTipText = "Double-click on me to SHOW this column"
ListboxVisible.ControlTipText = "Double-click on me to HIDE this column"


End Sub
Sub UpDate_List()
ListboxHidden.Clear
ListboxVisible.Clear
For i = 4 To 40
    If Columns(i).Hidden = True And Cells(7, i) <> "" Then ListboxHidden.AddItem Cells(7, i).Value
    If Columns(i).Hidden = False And Cells(7, i) <> "" Then ListboxVisible.AddItem Cells(7, i).Value
Next
ListBoxGroupsHidden.Clear
ListBoxGroupsVisible.Clear
For i = 4 To 40
    If Columns(i).Hidden = True And Cells(7, i) <> "" Then ListBoxGroupsHidden.AddItem Cells(7, i).Value
    If Columns(i).Hidden = False And Cells(7, i) <> "" Then ListBoxGroupsVisible.AddItem Cells(7, i).Value
    Next
End Sub
Private Sub CloseUserForm2_Click()
UserForm2.Hide
 
End Sub
Private Sub CommandButton1_Click()
UserForm2.Hide
Worksheets("Groups").Visible = True
Application.Goto Reference:=Worksheets("Groups").Range("A1"), _
 Scroll:=True
End Sub




Private Sub ListboxGroupsHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListBoxGroupsHidden.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = False
        
For i = 0 To ListBoxGroupsHidden.ListCount - 1
    If ListBoxGroupsHidden.Selected(i) Then
        ListBoxGroupsHidden.RemoveItem (i)
    End If
Next i
ListBoxGroupsHidden.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub
Private Sub ListboxGroupsVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListBoxGroupsVisible.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = True
        
For i = 0 To ListBoxGroupsVisible.ListCount - 1
    If ListBoxGroupsVisible.Selected(i) Then
        ListBoxGroupsVisible.RemoveItem (i)
    End If
Next i
ListBoxGroupsVisible.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub
 
Upvote 0
Could I please get some help on the next step? I got the groups loaded into ListboxGroupsVisible but I'm not sure how to make it so that when the list item is double-clicked, the competitor names in the group table become the competitors that hide on the my other sheet.
 
Upvote 0
I've been busy lately and away from computer. This forum will be offline Saturday. So I will try and get back with you on Sunday
 
Upvote 0
Well I see you have modified my orginal script in several place so now it's hard for me to work with your code because I'm not sure where all the places where you modified the code.

Like I used On initialize do this but you changed it to Activate.
But if you think that's important then so be it.

But it's hard for me to write code one way then you modify the code then want more code.

So tell me again What is the name of the sheet with all the group names
Group name being a Excel Table name
What is the name of the Listbox where you want all the Group names entered.

Then when you click on a group name you want all the values from that table loaded into what Listbox on the Userform.

And then what else do you need.

I will have to point out when you modify the code I'm providing and then need more code added to it makes it impossible for me to test things.
 
Upvote 0
Here is what my Two Groups looks like:

Two separate Tables one column wide:

AlphaDad
BravoMom
IndiaBob
JulietGeorge
KiloStanley
LimaJulia
Mike
November
Oscar
Papa
Quebec
 
Upvote 0
Or maybe better like this:
AlphaDad
BravoMom
IndiaBob
JulietGeorge
KiloStanley
LimaJulia
Mike
November
Oscar
Papa
Quebec
 
Upvote 0
AlphaDad
BravoMom
IndiaBob
JulietGeorge
KiloStanley
LimaJulia
Mike
November
Oscar
Papa
Quebec
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top