Excel VBA User Form - Dealing with multiple user selections in code

[h4fod]

Hi
I am developing a programmed solution using a graphical front-end which will allow users with limited competence using Excel to interrogate the underlying data set. This is a learner spreadsheet (ed context) with 280 learners (rows), 40 subjects (cols) with 5 cols relating to personal data (gender etc).

The system by way of 'looping' through the 'array' will search for and process specific learners grade outcomes etc and this works fine. However, my question relates to the structure of my program whem multiple search conditions are used. For example at least 6 check boxes and one combo box (with 4 options relating to ethnicity type.

This can imply considerable use of Case / nested case and/ or other constructs tio filter the data prior to processing with these multiple options.

Eg. Subject, Ethnicity (5 options), English, EnglishMaths, EnglishMathsAndScience etc..

Hope you can offer guidance on this

Many thanks in anticipation

 

[SkipVought]

hi,

Your primary obstacle is that your source data is NOT NORMALIZED!

You have "40 subjects (cols)" which imposes a huge mountain of effort (extra code), which you NOW need to scale.

Is this a database of your design, or one imposed by other circumstances beyond your control?

You might begin by educating yourself about databases.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Your objective is achievable without "considerable use of Case / nested case and/ or other constructs tio filter the data prior to processing with these multiple options." A simple query will return data for multiple subject criteria in addition to any other criteria, IF your source data is NORMALIZED.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[h4fod]

Hi
Thanks for your reply.

The spreadheet data set - which yes, is a flat file un normalised databased is output from a large MIS (which unfornately hasnt got this user friendly GUIwhich facilitates executive level analyses. So, rather than importing this data set Excel / CSV etc)into MS Access and using Access VBA / SQL etc I really wanted to contain the processing and filtering within Excel (not ideal I know).

Given this restriction, are you suggesting creating a spreadsheet within Excel on partly filtered data and then completing the processing options on this data set? Otherwise filters using Case / nested could involve c100 criteria, which from my progreamming experience is unwieldy! Is this ever done in the commercial programming community?
Thanks again