Hi Kevin,
See the attached .zip file which contains:
a. ExcelForumStudentData.xls - sample file for use with Excel 2003 ONLY.
b. ExcelForumStudentData.xlsm - the same sample file for use with Excel 2007 and later Excel versions.
c. 5 .bas files - Excel VBA modules (see below).
I finally finished testing and resolved all the known problems I had. Some of the problems I thought I had, seem to be a major design flaw in your worksheet design if almost any sheet is sorted. For example using my sample spreadsheet, if sheet 'PIPS' is sorted, the PIPS scores on Sheet 'Summative Assessments' no longer correspond to the proper pupil (See Pupil 'Mickey Mantle'). I have a macro that can resolve that problem.
Please do not feel hurried to give me feedback. I understand that the spreadsheet is complex, and that the spreadsheet priority is very low compared to your other duties.
However, if the Master List of Names (Sheet 'Add or Remove Pupil') is sorted, the data on other sheets is scrambled, and there is no way I know of with the current design, to unscramble the data.
I would suggest making frequent backup copies of your file, and suggest that sorting is not done.
The problem can probably be corrected with little or no change to your design, but would probably take a couple of months of intermittent work to implement.
Please note that the same problem seems to exist in your original file that contains NO VBA code.
-------------------------------
This update includes all software I have written for you on various threads. Hopefully, I've solved more problems than I have created. Your workbook design is excellent (except for the ability to scramble the data), but somewhat complex, and I hope I have not overlooked anything. Please let me know if anything is not working as you expected.
NOTE: Color created using CONDITIONAL FORMATTING CAN NOT be copied to another cell. CONDITIONAL FORMATTING stands alone. To put color in the 'Learner Report' Sheet for data on other sheets that uses CONDITIONAL FORMATTING, you have to add conditional Formatting to the 'Learner Report' Sheet.
Please let me know if you have any problems or questions.
Items of Note:
a. Adding students takes a relatively long amount of time. Progress is displayed on the 'Status Bar'.
b. I had problems with the formulas on the 'Sort' Sheet being corrupted. I emulated the formulas with VBA. The only columns used on the 'Sort' Sheet are Column 'E' (composite names) and new Column 'F' (reference to the row number on Sheet 'Add or Remove Pupil'.
c. It seems like the references to 'Surname' and 'Forename' on the 'Learner Sheet' may be reversed. See the fix below if this is the case.
d. Added OPTIONAL 'Pupil Count' in cell 'F1' of Sheet 'Add or Remove Pupil'.
e. Spelling Error on Sheet 'Learner Report' cell 'B20' 'Detailled Report' (two Ls)
To delete a module in the VBA Editor:
a. 'Left Click' on any cell in the Excel Spreadsheet.
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. 'Right Click' the module to be Deleted.
e. 'Left Clock' Remove ...
f. Select 'No' when asked 'Do you want to export ...'.
To import or export VBA Module Code:
a. To import, right click anywhere in 'Project Explorer'.
b. Select import file. Select a file to import.
Suggestions for testing the code:
a. Do preliminary testing on my Excel File (ExcelForumStudentData.xlsm ).
b. If successful, install the software on a copy of your file that contains actual data.
c. Since your formulas are somewhat complex, verify that pupil data is correct on the sheets that reference pupil data.
NOTE: Software Development was done using Excel 2003, which may have lost some CONDITIONAL FORMATTING colors, since Excel 2003 only allows 3 CONDITIONAL FORMATS per cell.
Instructions for installing the code in your file.
a. Make a backup copy of your file.
b. Extract the following .bas files from the attached .zip file.
(1) ModAddOrRemoveStudent.bas
(2) ModColorCodeSummAssessments.bas
(3) ModHowAreWeDoing.bas
(4) Module1.bas
(5) ModUtilities.bas
c. Open your file.
d. Alt F11 - to get to VBA environment.
e. CTRL R - to open Project Explorer (if not already opened).
f. In Project Explorer, 'Double Click' on 'Summative Assessments'. Cut and paste the following code into the 'Summative Assessments' module:
g. In Project Explorer, 'Double Click' on 'Learner Report'. Cut and paste the following code into the 'Learner Report' module:
g1.In Project Explorer, 'Double Click' on 'How are we doing in Maths'. Cut and paste the following code into the 'How are we doing in Maths' module:
h. Delete all the modules listed in b. above (in Project Explorer). 'Delete Module' instructions are included above.
i. Import the modules from b. above (in Project Explorer).
j. Save your file.
k. Done.
Additional Installation Instructions:
a. Make a backup copy of your file.
b. Open your file
c. Add pupil count (optional). On Sheet 'Add or Remove Pupil' Cell 'F1' add the following Formula:
d. Correct 'Learner Report' Formulas
(1) On Sheet 'Sort', change formulas in Column 'D' to use column 'B' first:
(2) On Sheet 'Learner Report', switch the formulas in cells 'C6' and 'C7':
e. Optional. Assign the following macros to those CommandButtons:
(1) AddOneOrMorePupilsToWorkbook() - to to 'Add Pupil(s)
(2) RemoveOnePupilFromWorkbook() - to 'Delete 1 Pupil'.
(3) CorrectCrossReferenceErrors() - to correct errors if '#REF!' appears in one or more Sheets.
f. Save your file.
g. Done.
Instructions for Use to ADD or DELETE Pupils:
a. You must be on Sheet 'Add or Remove Pupil'.
b. Select ('Left Click') any cell in the Workbook.
c. Press 'ALT f8' to get a list of Available Macros.
d. Select Macro 'RemoveOnePupilFromWorkbook()' and Select RUN.
e. The software will ask if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
f. Yes or No will determine that pupil's future.
g. Press 'ALT f8' to get a list of Available Macros.
h. Select Macro 'AddOneOrMorePupilsToWorkbook()' and Select RUN.
i. The software will ask for the number of Pupils to add. Select 'Cancel' or Press the 'Esc' key to not add any pupils.
If the workbook appears to be corrupt, running Macro CorrectCrossReferenceErrors() may correct the errors.
NOTE: If any formulas are added/deleted that reference a sheet other than Sheet 'Add or Remove Pupil', one or more lines MUST BE ADDED/DELETED from Sub CreateSheetAndCellDictionary(). The Sub contains Sheet Names and columns for the formulas, and also contains the formulas. In the formulas '~~~' is used instead of a row number.
Lewis
Bookmarks