See attached. It's work in progress.


On sheet SF 2 JUNE HARD COPY, two buttons in the vicinity of cell AT9.
Update triangles will add triangles
Reset will take them away and show all text so that users can comfortably edit them.
What Update triangles does is:
Takes each row in the range E16:AI66
then takes each cell in that row then
will only look at cells having a diagonal line, then it will set the font colour to white, then, only if the cell does NOT have a formula in it:
it will look at the value in the cell, take out forward slashes, remove all trailing spaces and all but one leading space, convert multiple spaces to a single space and then look up what's left in a dictionary of 100 or so possibilities. That dictionary contains the definitions; things such as Absent AM, Late AM, Cutting AM, Absent PM, Late PM, Cutting PM, which will be TRUE if they apply. There's also a column (rightmost) containing a standardised version of the string in the cell which will replace what's in the cell if it's different from that standard version. Having found the entry in the dictionary, it then goes about adding coloured triangles, red for cutting classes and blue for lateness. If there are both in one morning or one afternoon, the cutting classes triangle is halved in size and placed on top of the late triangle. Then if there are any absences (Xs) any X in the cell's text is made visible again, made super- or sub-script to place it comfortably in the cell. If the dictionary lookup doesn't find anything, that cell is coloured yellow (so that you can experiment and find combinations which should be found in the dictionary - tell me what they are and I'll add them).
When each row has been processed, cells in columns AK and AL are updated.


Try it.
I will speed it up later.



You can get an idea of what's in the dictionary by running the macro PrintTable, which will add a new sheet. Look-up values are in the left column.


The general protocol for entering text in the cells is that a space separates am from pm data, as does TI and LE which replace the space (because TI and LE don't have a morning/afternoon attribute). Otherwise the order in which L, C and X appear shouldn't matter. Where you have the likes of LL, it's assumed, because you can't be late twice in the morning, that one L is for the morning, and the other for the afternoon. The same applies to XX etc.

Attached Files

msofficeforums40597JUNE 2018 QUERYBoffer01.xlsm (133.6 KB, 10 views)

Big thanks for this cal, but the file is protected.


Got it. I'll get back to you soonest.

This is brilliant Cal, impressive. It is a far cry from the usual method of shading the shapes with crayons and calculating the daily learners movement with a calculator. My teacher friends will feel they have travelled to the moon and back once they got the hang of preparing their monthly reports through this automated system. I'v tried all the possible scenarios and I think everything is covered by the dictionary. The built in auto correction of any possible erroneous entry is what makes the system more amazing. Thank you.


A bit of correction in the formulae in AK and AL. Each instance of tardy is counted as 1 not .5, so AM tardy is 1, PM tardy is 1, AM and PM tardy is 2, while cutting class is counted as .5 like absences, so AM cutting class is .5, PM is .5, AM and PM is 1. cutting class is added to absences in AK. I'm sorry about this.

Try changing the EmbellishCell macro to:

Thank you again Cal. I feel guilty monopolizing your time in this forum. this thread had gone 4 pages already. I can't thank you enough so God bless you. I created a userform for data entry and I am now trawling the web for codes that would make the command buttons run.

Hi there.. I copied the enhanced macro that Cal posted for the SF2 into my own workbook. I tried creating a quick button for "reset" by inserting a shape >> assign macro>> then selected reset in the macro list but it jumped to a new module instead of the module in which I pasted the codes. I cancelled the new module, copied the "reset" button of Cal's worksheet then pasted it in my own. But when I clicked on the reset button, it opened Cal's file. How do I create a button from the "reset codes" of my own sheet?

You did paste it into a standard code-module didn't you? (Not a sheet's code-module, and not the ThisWorkbook code-module.)



I don't understand 'jumped to a new module'





'cancelled'?! What does this mean?





There are 3 ways you can try to be sure that you're choosing the right macro in that dialgue box:
1. Choose This Workbook in the Macros in: field:


2. Close all workbooks except the one you're working on.
3. Rename the copied macro to say ResetMe then you'll be able to identify it for certain in that dialogue box.


(Who's Cal? He seems to just beat me to it each time, and yet I can't see any of his posts.)