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?

 

[gaudrco]

The user can create new competitors. So right now, there are about 16 columns but next year there might be 40.

You know the values stored in a UserForm are lost when you close UserForm.

I understand this but I'm certain there is a workaround.

Maybe I can have a place on one of my sheets where I can add the competitor names to a column and name that column whatever group name I want it to be. Then add code to the existing listbox code that works like this:
- Match the cells in the group column with the cells in the table (where I want to hided/show columns), and take the name from the grouping header.

So if I use a table for grouping, the headers would be the group name and the data would be the competitor names.
Ex: In the Grouping Table, I name the first header "Fruits" and for the data under the header I enter; Apple, Pear, Orange, Grape. Then when I open the Userform, the code recognizes that Apple, Pear, Orange, Grape from the Grouping Table matches the cells in row 7 on the table for hiding/showing competitors. The code takes the name "Fruits" and it becomes an option in the listbox items. So when a user doubleclicks "Fruits", all of the fruits listed in the Grouping Table show/hide.

Just an idea. Do you think you could get it to work? Would you adjust anything?

 

[My Aswer Is This]

Well this could be done. Using a table. But each Group would need to be a separate one column Table

Then we would have to load in the Table names into a listbox on the Userform so then the user could choose which Group of values he wanted in the listBox

So would this list be the columns he want to see or the columns he wants to hide

I would suggest these Tables be on another sheet like maybe sheet named "Groups"

 

[gaudrco]

So I created a spot for the grouping tables. They are located on sheet ("Information") starting at column AA. The group name headers are in row 2.
Question: Do the group tables need to be actual tables? Can they be unlabeled ranges (undefined)

How would the code be written to add the group name to the listbox and matching the name in the grouping table to hide/show those competitors?

 

[My Aswer Is This]

The Groups need to be actual Tables.
And give the Table a name like:

Cars
People
Countries

A name that identifies what's in the Table

We need to use a Table so as you later add more values to the table the range expands down.

Each table only needs to be one column wide

I want to use the Table name as the name of the Group not the value in the first row of the Table.

Now if we later load these group values into a Listbox what do you plan to do with the values in the listbox?
Do you plan to click one button to hide them all with one click or unhide them all with one click. Or both?

And then why do we need to load all the values into a listbox?

Why not tell the script to look in Table named Cars and hide all those values in the Table

The values in the Table being values you find in Row(7)

So you would have a list of the Group names in a listbox and just double click on Cars in the listbox and it would hide or unhide all the columns with Car names on row(7)

And you said the Table started in Column AA of a sheet named

Information

Why such a far to the right column?

You know if a user decides to delete Row 5 and part of the Table is in row(5) that row in the table will be deleted.

​

​

 

[My Aswer Is This]

When you ask for help I like a challenge to achieve what users want.

But I like when I decide a way to achieve the Goal.
In your previous post you mentioned wanting to use a Table which is a good ideal.
But then in your next post you said can we use some sort of dynamic defined range.

I do not know how to create a dynamic named range without using a Table and am not sure now why you think it needs to be done this way.

I have already tested a way to do this with a Table.
And the sheet with the Tables need to have only these Tables and not addition Tables

This way I write a loop that says enter all the Table names on sheet named "Group" or something like this.

If we do not do it this way then every time you want to make a new Group you need to modify the script.

And on my test new script I have a Listbox for all the Group names which are the Table names.
Then my script has a button that then when clicked loads all the Group values in another Listbox.
Then if you click a button all the columns in the listbox will be hidden or unhidden.

But it's very hard if you ask for things then change your mind the next time you post. With no logical reason given on why you now want to change your mind.

 

[gaudrco]

Thank you for your posts, My Aswer Is This.

Much of what I know about advanced excel methodologies comes from forums. So I apologize if use the wrong terms (dynamic defined range) to describe things. I will try to be more clear about what I'm looking to do.

Why not tell the script to look in Table named Cars and hide all those values in the Table

This is what I'm looking to accomplish. I have created a sheet called ("Groups") and I created 1-column width tables starting on cell ("A2"). So the table header right now is called ("Group1") and the Table Name is also called ("Group1"). I would like it if the user could change the name of the header and that would change the name of the table.

I have a new table every other column. So the first table, ("Group1"), starts on cell ("A2"), the next table, ("Group2"), starts on ("C2"), the next one ("Group3") on ("E2"), and so on. Right now I have 15 tables.

All of the data cells for every table are data validation list of values. The reference list is the list of competitors on my spreadsheet. This way, I force the user to choose the correct competitor name without having to worry about them spelling it wrong (if they were to type it in manually) Separately, I would like to make it so that a user can't choose the same value more than once in the dropdown. If they choose ("Apple"), then when they click the next dropdown in that same table, ("Apple") would be removed from the list.

Now if we later load these group values into a Listbox what do you plan to do with the values in the listbox?
Do you plan to click one button to hide them all with one click or unhide them all with one click. Or both?

I plan for the new Group names to appear in the current listboxes (ListboxVisible and ListboxHidden). In a situation where all competitors are showing (meaning no column is hidden), I would expect that all the loaded group names would appear in ListboxVisible. When I double-click any value (single competitor or group), the list item will move over to the other listbox, ListboxHidden. This is currently how the listboxes work right now with all the single competitor list items. If a user were to double click ("Group1"), I would expect the list item ("Group1") and all the associated competitors to move over to ListboxHidden.

I think I covered everything but if something is still unclear, let me know

 

[My Aswer Is This]

When dealing with Table we do not need to know where the table starts or ends.

If I were to write code to find the table all I need to do is write code like this.

Sub Select_Me()
'Modified  11/11/2019  12:21:08 PM  EST
Sheets(1).ListObjects("Cars").Range.Select
End Sub

As far as naming a Table by entering a value into the Top cell of the Table like you want to do.
I think would be impossible. So any time a user change the value in the Top cell of the table the Table name would change. If that's possible I do not know how to do that.

To load all the table names into a listbox I would write a script which would look into sheet named "Group" for example and find all the tables and then load the table name into the listbox.

Something like this:

Sub Select_Me()
'Modified  11/11/2019  12:40:17 PM  EST
Dim T As ListObject
Sheets("Group").ListBox1.Clear
    For Each T In Sheets("Group").ListObjects
        Sheets("Group").ListBox1.AddItem T.Name
    Next
End Sub

So where the Table is located is not important to me.

And with this script if you add more Tables to the same sheet those table names are always loaded into the listbox. Now in our case the listbox would be on a UserForm.

So the first problem is I see you want all the tables to be give the name of the Top cell in the Table.

And I see no way we can do that. Especially being done by just having the user do it by just changing the value in the top cell in the Table.

 

[gaudrco]

As far as naming a Table by entering a value into the Top cell of the Table like you want to do.
I think would be impossible

That's okay. I think I can work around this.

I'm not sure exactly where to enter the code you created. Here is the code I currently have:

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
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
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

 

[My Aswer Is This]

The script I just provided was just a sample so you would see how code looks for doing certain things.
It was not intended for use in the Userform code we are working on.

So are all your Tables in a sheet named Groups?
And there are no Other Tables on that sheet which we want to exclude.
So we can tell the script to load all Table names on that sheet into our Userform ListBox.

Now we are going to need a new Listbox on the Userform to load all these Group names.

So will that be ListBox Named Listbox3 or some other name.

And then what do we want to do.

Do you want to be able to click on a group name which will then enter all those names in the group into another listbox on the userform?

If so we will need another listbox on the Userform.

We will have to get back to some of your other requests later.

Like you said:

All of the data cells for every table are data validation list of values. The reference list is the list of competitors on my spreadsheet. This way, I force the user to choose the correct competitor name without having to worry about them spelling it wrong (if they were to type it in manually) Separately, I would like to make it so that a user can't choose the same value more than once in the dropdown. If they choose ("Apple"), then when they click the next dropdown in that same table, ("Apple") would be removed from the list.



We have never talked about anything like this. All our previous post talked about Userform and hiding columns.​

 

[gaudrco]

So are all your Tables in a sheet named Groups?
And there are no Other Tables on that sheet which we want to exclude.
So we can tell the script to load all Table names on that sheet into our Userform ListBox

Correct.

Now we are going to need a new Listbox on the Userform to load all these Group names.
So will that be ListBox Named Listbox3 or some other name

I created a new listbox called ("ListboxGroupsVisible"). The new listbox is on ("UserForm2")

Do you want to be able to click on a group name which will then enter all those names in the group into another listbox on the userform?
If so we will need another listbox on the Userform.

Yes I will add another listbox on ("UserForm2") called ("ListboxGroupsHidden"). These two listboxes should behave just like the other two. When I double click the group name, the group name list item should go from ("ListboxGroupsVisible") to ("ListboxGroupsHidden").