Solved

XL Userform to Create Sheets & Populate with Defined Data

Posted on 2014-03-11
11
323 Views
Last Modified: 2014-03-11
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
0
Comment
Question by:DrTribos
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39920088
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
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39920194
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39920249
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Author Comment

by:DrTribos
ID: 39920306
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
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39920375
ok

Is this what you want ?

1) Activate Macroes.
2) Make a selection in First Combo (select a category) then try all kind of combination press on Create category without any other selection, make selection and see results,
3) Make more selections
4) Press on Reset selection to reset all highlighting from the listbox
5) It is Dynamic like add data to  Col A or B or C ... and see how it add automatically in all listbox and combo.

Let me know
gowflow
Demo-V01.xlsm
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39920470
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
0
 
LVL 29

Expert Comment

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

gowflow
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39920506
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39920703
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
0
 
LVL 15

Author Closing Comment

by:DrTribos
ID: 39921859
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39921907
Your welcome glad it worked for you.
gowflow
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question