Solved

XL Userform to Create Sheets & Populate with Defined Data

Posted on 2014-03-11
11
319 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 14

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

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 14

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 14

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 14

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
formattig excel from access 3 17
Copy the formula in excel 8 29
Gantt chart 2 16
TT Formula Check 9 4
This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now