Creating a group for items in a listbox

[gaudrco]

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?

 

[My Aswer Is This]

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.

 

[gaudrco]

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

 

[gaudrco]

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:

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

 

[gaudrco]

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.

 

[My Aswer Is This]

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

 

[My Aswer Is This]

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.

 

[My Aswer Is This]

Here is what my Two Groups looks like:

Two separate Tables one column wide:

Alpha	Dad
Bravo	Mom
India	Bob
Juliet	George
Kilo	Stanley
Lima	Julia
Mike
November
Oscar
Papa
Quebec

 

[My Aswer Is This]

Or maybe better like this:

Alpha Dad Bravo Mom India Bob Juliet George Kilo Stanley Lima Julia Mike November Oscar Papa Quebec

 

[My Aswer Is This]

Alpha	Dad
Bravo	Mom
India	Bob
Juliet	George
Kilo	Stanley
Lima	Julia
Mike
November
Oscar
Papa
Quebec

 

[My Aswer Is This]

Or like this: