VBA for creating a table using the parameters from checkbox

[ajoshi76]

Hello

I have the following parameters which should be checkbox or a way to have multi - selection

Application : A1 to A8
Location : L1 to L7
Role : R1 to R7
Features : F1 to F3
Components : C1 to C5

* Components are dependant on Features
* every Role can have multiple location selected

The Table that should be populated in another sheet is as below:

If A1, A2, A7, A8 are checkmarked, Location L1, L2, L3 are check marked, Role R1 to R8 are check marked, Features F1, F2 are check marked and its respective components C1 and C3 are checkmarked - the table that should be populated is as attached

 

[Hui]

Have a look at the attached file
I think it does what you want ?

 

[ajoshi76]

Thanks Hui - i am using excel with Mac - unable to customise the form - any idea how to do it?

 

[Hui]

No idea

I've never used a Mac

 

[ajoshi76]

Thanks Hui... I am working on an entire template for automation - would need you to help me if possible. shall upload the template tomorrow.

 

[vletm]

ajoshi76
Where do You need to customise that Hui's form?
You can use that form with Mac too - tested.

Here is sample, how to use worksheet as form, without any checkboxes or so.
Select numbers one-by-one as I've done Columns H .. L.
Can You verified that Your given sample result is correct?
( except there should be Role: R1 to R7 and You have marked Roles R1 to R8 )
This won't do table, because missing Your logic of that.

 

[ajoshi76]

Thanks vletm ... The dataset was just a sample - i apologise if i have created incorrect dummy values
I am currently working on a template which then would require automation. Could i get back to you and hui on this thread in a couple of days.

 

[vletm]

ajoshi76
It would be always better if even sample file is correct.
It takes time to get 'same incorrect results' ... and ... that would be sometimes very hard.
With my version, it would be possible to modify those parameters without any specific tricks.
I can try to add Your needed automation soon.
I try to skip Your 'the table that should be populated is as attached'!

Here is my sample for testing ...
> You can add as many numbers as needed per columns H ... L.
> You can click any of those numbers per those column ... yellow is selected ... otherwise not
> Press [ ReFresh ] to refresh Columns A:E results.
> Not fully tested!

 

[ajoshi76]

Hui, Vletm,

Attaching a better version of the template i am working on. Hope this clears all the issues related to data quality and selections and other related fields.

1) Sheet "Form" : The Components list box to be populated with the values from "Global Config" sheet using FC_List and its dependant on the Features Combo box in the sheet.

2) Sheet "Form" : When we click "Process" button the following actions to be taken by the macro:
a) "Location Split" sheet to be updated with Application Name, Role, Location
b) "Assessment" sheet to be updated with Feature, Components, Application, Roles
c) "Budget" sheet to be updated with Feature, Application, Role


3) Sheet "Form" : When we click "Reset" Remove all data from all sheets wherever we are updating based on the "Process" button

4) When we keep select the data from the "Form" sheet and keep clicking "Process" button - It should append on the 2a), 2b) and 2c)

5) If there is duplication or human error in selecting same drop downs again when clicking "Process" Button - it should only append the ones which are not there in any of the other 2a), 2b) and 2c)

Thanks a ton for your help
Ashish

 

[vletm]

ajoshi76
Where is connection with Your Sample-file as well as what You maybe tried to find as I tried to show for You with my Sample.xlsb?
You missed to give any comments from that...
I can find some same terms which You used with Sample-files.
Could You show again some realistic sample results with Your given sample values?

 

[ajoshi76]

The logic is not that simple - as the sample file does a permutation & combination of all respective values per feature, component etc. generically based on selection.

The template i have attached latest is what i see as the final requirement of it to be working and with the right set of combinations

If you could help me using my latest file, it would be great help

 

[vletm]

ajoshi76
... do You mean that there is no connection - or did You skip that question as next one too?
What would mean Your term 'simple'? ... is that something which is possible? ... or what?
Did You notice my the last line`
Could You show again some realistic sample results with Your given sample values?
I need there some real sample ... which might show that Your named 'not that simple'
... as well as expected results based Your sample values.

 

[ajoshi76]

i shall populate the results in the spreadsheet and re-attach by tomorrow but the functioning is way the permutation combination and selection works is not by select values per parameters and put all in the table.

Components depends on Features and Locations depend on Role and the selections on the Form sheet is very important to drive the automated population of data.

Give me one more day, shall upload the updated spreadsheet with the desired values.

 

[ajoshi76]

I am going to split the request to ensure step by step VBA code for the same.

Requirement#1
Form II Sheet has a ListBox for Components
Based on the Feature Selected it should populate the corresponding values from Global Config sheet (Feature-Component Table) into the list box on Form II Sheet.

 

[ajoshi76]

Requirement #2

The user would select :
1) Feature : Data Sourcing
2) Components : Multiselect values from Listbox - Values that would be selected are : Feed File - Existing (Enhanced), Adjustments - Concise Template and Table & Views - This values would appear in the list once the Requirement#1 is coded.
3) Application : Application#1
4) Role: IT Business Analyst (3 times) as per the template
5) Location : Switzerland, Singapore, London
6) Respective percentages per location

Click on Process Button...

Output required:
1) Refer "Location Split" Sheet to update Application, Role, Location, %
2) Refer "Assessment" Sheet to update Feature, Components, Application, Role
3) Refer "Budget" Sheet to update Feature, Application, Role, Location

The user can again goto Form II and select data for Application#1 and Caculation and Processing as Feature. The data should append in those sheets.

 

[vletm]

ajoshi76
Third time: Could You show again some realistic sample results with Your given sample values?

Your I am going to split the request to ensure step by step VBA code for the same.
If You know - how would something to do ... then start Marco Recorder.
or have You done VBA codes before?

You write write ... but You should give same time something - which shows what?

Click on Process Button...
...
and next You wrote Output required:
but how those comes?

Sorry, with those writings
... I didn't get answer to my basic question ... or ... any?
You seems to ask to create an engine ...

I noticed that You has changed layouts - that's no cleaver
- You should have a clear idea - what? - how?

 

[ajoshi76]

i am not able to understand your questions... If you refer my last attachment i have given real sample values in the sheets that need to be populated using the Process button on the Form II sheet.

The template i have attached is the final layout and working model i would like to achieve.

I have also written that the user needs to use Form II sheet to select the values and then when Process button is clicked it should update the 3 sheets which i have already explained in detail.

Now i am confused, what did i not explain (which i already did in so much detail).

 

[ajoshi76]

Check this file - with values in each sheet (output) after the process button is clicked.
Form II where the Process button is - would be the place where the values need to be selected and then once process button is clicked the 3 sheets to be populated.

Requirement #2

The user would select :
1) Feature : Data Sourcing
2) Components : Multiselect values from Listbox - Values that would be selected are : Feed File - Existing (Enhanced), Adjustments - Concise Template and Table & Views - This values would appear in the list once the Requirement#1 is coded.
3) Application : Application#1
4) Role: IT Business Analyst (3 times) as per the template
5) Location : Switzerland, Singapore, London
6) Respective percentages per location

Click on Process Button...

Output required:
1) Refer "Location Split" Sheet to update Application, Role, Location, %
2) Refer "Assessment" Sheet to update Feature, Components, Application, Role
3) Refer "Budget" Sheet to update Feature, Application, Role, Location

The user can again goto Form II and select data for Application#1 and Caculation and Processing as Feature. The data should append in those sheets.

 

[vletm]

ajoshi76
Sentences which ends with question-mark are questions.
Your have skipped many times few of those.
eg have You done VBA codes before? Is basic Yes/No-question.
Which part of that question, You do not understand?

Requirement#1
Form II Sheet has a ListBox for Components
=> If fixed selection ... then why You did not make fixed settings for that?
or ... if 'Features' selection would use for something else ... then ... that minor detail should know ... or ... it will be always fixed!

Requirement #2
The user would select : ...
Okay, like fixed selections again.

Click on Process Button...
How would get those required Outputs?
"Location Split": Your sample results has four lines data ... from three to four?
"Assessment": Someway more mystery sample results as above...
"Budget": Same as above...
> It would take time to find Your logic, where/how You have gotten those sample results - based Form II or somewhere?
> eg more basic like Your sample: Your give numbers 2 and 4 and 6 - okay? Finally, You give sample result: 5 - okay?
... You do not explain: how to get 5 ... from those given three numbers .... and ... if those three number could change ... would result still be 5 or something else?