Avatar of DrTribos
DrTribosFlag for Australia asked on

XL Userform to Create Sheets & Populate with Defined Data

Hi All,

Given time I think I could figure this out with VBA but if someone can provide concept code that would be great.

I have attached a spreadsheet - it has a "Main" page with 3 columns of data (Category, Attribute & Property).   The idea is to use the main sheet to specify data to other sheets (new or existing).  

Each sheet represents a category (in other words each category gets its own sheet).  The data in the sheet is UNIQUE combinations of Attribute and Property.

Listbox 1 is used to select or create a new sheet (Category)
Listbox 2 & 3 are used as data on the new sheet (concatenate Attribute & Property)

In my demo file I have manually generated a sheet called Category1.  I hope to have a userform that is able to manually add to the category if the unique combination does not exist for that category.

I envisage single selection on Category, single selection on Attribute, and multiple selection on Property (combinations that already exist in the category would be indented and not selectable).
Pressing OK would:
 Create / Access a sheet with the name of the selected category
 Add the unique combinations of concatenate(Attribute & ":" & Property) to the category.

Confused? Pls let me know if you have questions.

Cheers,
Demo.xlsx
Microsoft ExcelMicrosoft OfficeVisual Basic Classic

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
gowflow

Confused ???

Well you should say 'Not Clear'

First you say:
In my demo file I have manually generated a sheet called Category1

in the file it is Category2 not Category1

Second
Listbox 1 is used to select or create a new sheet (Category)
Listbox 2 & 3 are used as data on the new sheet (concatenate Attribute & Property)

Where are these listboxes ?? Is do not see them !!

So I suggest you go step by step
try to explain a bit clearer

My understanding as a whole is that you have data in Main and you need to create a worksheet for every Category and name it with the Category name

This sheet will hold some data but it is not clear at all what it will hold as already the data in Sheet Main is not setup as database (Columns and rows) but simply as scattered items.

Last but not least this concept of
'concatenate Attribute & Property'
or
'Add the unique combinations of concatenate(Attribute & ":" & Property) to the category'

is also NOT AT ALL CLEAR !
gowflow
ASKER
DrTribos

Hi gowflow

Thank you for taking the time to review the question... appologies for the lack of clarity.

in the file it is Category2 not Category1
Sorry - typo

Listbox 1 is used to select or create a new sheet (Category)
Listbox 2 & 3 are used as data on the new sheet (concatenate Attribute & Property)

Where are these listboxes ?? Is do not see them !!
Sorry again - I was speculating on a solution I had not put in a listbox.

My understanding as a whole is that you have data in Main and you need to create a worksheet for every Category and name it with the Category name
Correct

This sheet will hold some data but it is not clear at all what it will hold as already the data in Sheet Main is not setup as database (Columns and rows) but simply as scattered items.
Each sheet that is created will contain scattered data, true.  There are about 30 "categories" and 100s of "Attributes" & "Properties" so the amount of combinations is large.

'Add the unique combinations of concatenate(Attribute & ":" & Property) to the category'
Yes - add the unique combinations to the category sheet.
gowflow

Well tks for replying but still not clear.

Last part

What do we create or what do we put in the sheet Category that is created out of the sheet Main that is the question !

Reading again you original post I think I almost got what you want.

You need to select a category then from 2 list boxes choose 1 Attribute and then highlight possibly 1 or many Properties
and then

You want on an activation of a button say (Create Category) for:

1) A sheet Category to be created with the name being the Category choosen
2) The Data in that sheet will consist of
Col A Category choosen
Col B Attribute Choosen
Col C all the Properties highlighted

If above is correct then I have following questions:

1) When we create a Category do we delete the info that is Create from sheet Main ?
2) If answer to 1) is NO then if the user select a Category that was previously created what do we do ?
 A- Add the new data after the last existing one already ?
 B- Delete the Exiting data and replace it by the new data ?

Let me know.
gowflow

gowflow
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
DrTribos

Hi gowflow

Thanks for your questions.  
1) When we create a Category do we delete the info that is Create from sheet Main ?
 A- Add the new data after the last existing one already ?
No - we keep it in case we need to add more attributes and properties.
A category sheet can have many entires, this might change over time.

You want on an activation of a button say (Create Category) for:

1) A sheet Category to be created with the name being the Category choosen
2) The Data in that sheet will consist of
Col A Category choosen
Col B Attribute Choosen
Col C all the Properties highlighted
Not quite. There would be 1 column which is the concatenation of B & C (as in the demo sheet.

thanks
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
DrTribos

Hi yes,

very close... the only thing missing is that I can select multiple properties for a single attribute and have the sheet updated with each... e.g select Attribute D and Property A, B, D & E (multiselect) and the sheet will be updated with:
AttributeD:PropertyA
AttributeD:PropertyB
AttributeD:PropertyD
AttributeD:PropertyE

Thanks
gowflow

well multi select the property !!!
click a property and drag .... more not contiguous press shift CTRL and click on the extra item.

gowflow
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DrTribos

Hi gowflow

I'll look in the morning,  well actually it is already morning here. I'll look at it again when I wake up. Thanks for sticking with it. I did not look at the vba, only check the function. My guess might be incorrect but thought the macro might not support multi select.
Thanks again for your help
gowflow

Well don't look at the code !!! it is not there !
activate macro and in List of Property select several items at the same time like click and while clicking drag it down

Does it highlight several items ? or only 1 at a time ?

If it does not highlight several items at the same time then:

What version of Excel are you using ??
gowflow
ASKER
DrTribos

Perfect - and also looking at your code I would never have made something so sweet, not this week anyway.  I am much better in Word than I am in Excel.  Thank you for sticking with my unclearness.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gowflow

Your welcome glad it worked for you.
gowflow