Link to home
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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
Avatar of DrTribos

ASKER

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.
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
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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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
well multi select the property !!!
click a property and drag .... more not contiguous press shift CTRL and click on the extra item.

gowflow
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
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
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.
Your welcome glad it worked for you.
gowflow