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?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
Sorry for late response, the email notification from MrE of your last post went to my spam folder and I didn't see it.
No, I haven't done anything towards this other than think you would need two columns for each group, one for the competitor name and one that indicates visible or hidden.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
No problem, thank you for responding. So what you are saying is each table for competitor groups would have 2 columns, one for the name of the competitor, and the other would be either TRUE or FALSE. The TRUE/FALSE formula would look at sheet ("Competitor Comparison") to determine whether the second column should say TRUE (the column is visible) or FALSE (the column is hidden)

Is this what you are saying?
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Would there be a way to code this logic:

When the list item (Group Name) is in ListboxGroupsVisible, then SHOW all the columns on sheet ("Competitor Comparison") that match the data in the Groups table (the data in the groups table are competitor names). If a list item is double clicked, then move the item from ListboxGroupsVisible to ListboxGroupsHidden. When a list item is in ListboxGroupsHidden, then HIDE all the columns on sheet ("Competitor Comparison") that match the data in the Groups table. If a list item is double clicked, then move the item from ListboxGroupsHidden to ListboxGroupsVisible
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

gaudrco,

Am I correct in thinking your end goal is to have sheet "Competitor Comparison" show results for specific competitors
(determined as a group, and arranged according to position in that group) for a specific report ?
That there would only be one report for one group shown at a time ?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
In that case, if this were my project, and it isn't, I'd change the raw data layout to be more database like and more VBA friendly,
ie: a single table, no blank columns, no blank rows and no merged cells.
This would make your end goal a matter of simply filtering and copying to the comparison sheet.
No need for 5000 formulas or any showing or hiding of columns on the comparison sheet and no sorting of anything left to right on any sheet as that can all be handled by vba when copying.

If you're interested in that approach, here's something I cobbled together over the weekend for something to do while I wasn't doing anything.
In the mocked up raw data the reports are 'scrambled' to show they don't have to be in order like in your original. In fact the only real difference is the first column each line has the report name instead of merged cells.
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Thank you for the suggestion. It sounds like your way makes a lot more sense than my way. I'm trying to wrap up this project because my contract is almost up so I don't think I will have time to implement this idea.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,078
Messages
5,628,536
Members
416,323
Latest member
65563

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
Top