[sims] SIMS Profiles Templates

[cinnamonmb]

Hi

 

I realise that when using SIMS profiles you cannot choose to bring the colour formatting that may be contained in the marksheet (for example if a marksheet is formatted to show red, amber or green depending on if a student is on track to meet their target grade or not). In the word template we bring across the description of red, amber or green but we cannot bring the colour across. You can do this in Individual Reports in SIMs but not Profiles. Our SLT want to still ensure that the full profile is visual and that the on track cell is shaded the appropriate colour (Red, Amber or Green) as well as containing the description. I agree that this has impact and gets the message across instantly to students and parents. However, I'm keen to avoid having to manually amend this on over 100 reports for each subject (yuk!). Does anyone have a macro in word that searches through a table in word for and if it finds a certain value to shade the cell a colour based on that value? The macro should then go to the next table in the document and repeat the process. I've spent ages googling this and can't seem to find anything that fits the bill and my VBA skills are basic!! Any help is gratefully received

[vikpaw]

There are some threads on here where i think there was some Macro code to do something similar, if not i'm sure it has also appeared on SupportNet, however the search is rubbish and i couldn't find anything when i just checked.

This is from a chappie in the far east, Ian, i'm sure he won't mind me sharing it. I've not tested it and there are some caveats, plus i've edited as the copy and paste didn't layout quite right. Where it says SHADED it should just be shaded.

 

I'm sure you're supposed to be able to add the macro into the auto() sub so it works all the time but i never got that to work. @LosOjos may also be able to advise.

 

We don’t have ticks but instead have shaded boxes. You could easily adjust it but I prefer the shaded boxes!

[TD="align: center"]SHADED
[TD="align: center"]
[TD="align: center"]


[TD="align: center"]Spelling
[TD="align: center"]3
[TD="align: center"]
[TD="align: center"]
[TD="align: center"]SHADED
[TD="align: center"]
[TD="align: center"]


[TD="align: center"]Grammar
[TD="align: center"]4
[TD="align: center"]
[TD="align: center"]
[TD="align: center"]
[TD="align: center"]SHADED
[TD="align: center"]

		Support Required	Slightly Below	At Expected	Slightly Above	Above
Handwriting	1

 

In the second column is the data from SIMS, that teachers enter on their mark sheet. I set the formatting on the font so it is very small and white and I format the table so that it doesn’t look like that column is there.

 

Then, in the Individual Report, you need to insert the macro that I’ve pasted below. What it does is:

1. Count how many tables there are and loop the code around each one in turn. For each table:

a. Count how many rows there are and loop around the rows in turn. For each row:

i. Look at the value in column 2 and take the first digit (just to clean it up a bit)

ii. Based on that value, shade in a specific cell.

It goes in here:

Project(IndRepStudentTemplate)

- Microsoft Word Objects

- This Document

 

Caveats:

· You must have Macros fully enabled.

· The colouring in script runs when word opens. This means that it colours in when you go to Preview, or Print. If you save then it will run when you double click on the document.

If you press upload, then it does not store it ‘coloured in’. In theory, it should work when you open it. However, currently this is broken! I haven’t looked at this yet, but if anyone has a solution please let me know!

 

Private Sub Document_Open()
On Error Resume Next

'Count how many tables there are in the document
intNumTables = Application.ActiveDocument.Tables.Count

'Set up a loop to repeat the code for each table in the document
For intCurrTable = 1 To intNumTables

'Make all subsequent commands (e.g. .rows .cell) relate to the current table
With Application.ActiveDocument.Tables(intCurrTable)
       
  
   'Count How many Rows there are in the current table
   intNumRows = .Rows.Count
  
   'Set up a loop to cycle around all the rows in the current table
   For intCurrRow = 1 To intNumRows
       'Look at the cell value of the 2nd column
       strCellvalue = Left(.Cell(intCurrRow, 2), 1)
      
       Select Case strCellvalue
       ' Based on the number in that cell (1,2,3,4 or 5) colour in the correct cell
           Case "1"
               .Cell(intCurrRow, 3).Shading.BackgroundPatternColor = RGB(214, 227, 188)
              
           Case "2"
               .Cell(intCurrRow, 4).Shading.BackgroundPatternColor = RGB(214, 227, 188)
              
           Case "3"
               .Cell(intCurrRow, 5).Shading.BackgroundPatternColor = RGB(214, 227, 188)
           Case "4"
               .Cell(intCurrRow, 6).Shading.BackgroundPatternColor = RGB(214, 227, 188)
           Case "5"
               .Cell(intCurrRow, 7).Shading.BackgroundPatternColor = RGB(214, 227, 188)
       End Select
      
          
   Next intCurrRow
  
End With

Next intCurrTable
End Sub

[cinnamonmb]

Thanks Vikpaw! I tried searching on here too and couldn't find anything. Will try this tomorrow when I'm in the office and feedback to you