Sandtree
08-03-2016, 07:08 AM
I've been asked to help with a modestly complex questionnaire which will take the form of a spreadsheet with 3 visible tabs:
Instructions
Questionnaire
Dashboard
There is one additional hidden tab that contains lookup tables, and tables to generate the hybrid graphs that appear on the Dashboard. The results are driven by a percentage but the percentage itself is hidden and only the associated text is shown (beginner, proficient, advanced etc)
The person that created it has password protected the structure and individual sheets (I have the password)
Originally the intent was that each group would complete one questionnaire per group and the collated results will be done as scatter graphs which was all fine. Some groups have decided that they want each individual to do their own questionnaire and they will then take a simple average of the 8 scores it generates. The number in each group varies from 3 to 15 or so
I thought this would be simple so created a new file with some VBA where they could copy the assessment sheets and the VBA then extracts the hidden 8 percentages into a dynamically created table, averages the scores, works out the overall results and creates the dashboard.This bit, i've managed and it looks good etc.
Now the problem. In the original questionnaire workbook you cannot copy the sheet because of the protection on it :banghead:
Not keen on giving out the password to everyone and dont fancy having to receive, open, unprotect and copy potentially 400 or more workbooks. How can I systematically allow users to copy in the Questionnaire sheets from other files? I cannot assume that people are saving the files with sensible names, all in the same folder and/or dont have other spreadsheets in the same folders. If I had the file name with full path the VBA doesnt look too bad but dont know how to create a method of capturing the full path/filename as asking users to freetype into a text box wouldnt be acceptable.
Many thanks
Instructions
Questionnaire
Dashboard
There is one additional hidden tab that contains lookup tables, and tables to generate the hybrid graphs that appear on the Dashboard. The results are driven by a percentage but the percentage itself is hidden and only the associated text is shown (beginner, proficient, advanced etc)
The person that created it has password protected the structure and individual sheets (I have the password)
Originally the intent was that each group would complete one questionnaire per group and the collated results will be done as scatter graphs which was all fine. Some groups have decided that they want each individual to do their own questionnaire and they will then take a simple average of the 8 scores it generates. The number in each group varies from 3 to 15 or so
I thought this would be simple so created a new file with some VBA where they could copy the assessment sheets and the VBA then extracts the hidden 8 percentages into a dynamically created table, averages the scores, works out the overall results and creates the dashboard.This bit, i've managed and it looks good etc.
Now the problem. In the original questionnaire workbook you cannot copy the sheet because of the protection on it :banghead:
Not keen on giving out the password to everyone and dont fancy having to receive, open, unprotect and copy potentially 400 or more workbooks. How can I systematically allow users to copy in the Questionnaire sheets from other files? I cannot assume that people are saving the files with sensible names, all in the same folder and/or dont have other spreadsheets in the same folders. If I had the file name with full path the VBA doesnt look too bad but dont know how to create a method of capturing the full path/filename as asking users to freetype into a text box wouldnt be acceptable.
Many thanks