+ Post New Thread
Results 1 to 3 of 3
MIS Systems Thread, SIMS Profiles Templates in Technical; Hi I realise that when using SIMS profiles you cannot choose to bring the colour formatting that may be contained ...
  1. #1

    Join Date
    Sep 2012
    North Yorkshire
    Thank Post
    Thanked 0 Times in 0 Posts
    Rep Power

    SIMS Profiles Templates


    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

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Saudi Arabia
    Thank Post
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    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!
    Support Required Slightly Below At Expected Slightly Above Above
    Handwriting 1 SHADED
    Spelling 3 SHADED
    Grammar 4 SHADED

    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:
    - Microsoft Word Objects
    - This Document

    ∑ 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

  3. #3

    Join Date
    Sep 2012
    North Yorkshire
    Thank Post
    Thanked 0 Times in 0 Posts
    Rep Power
    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

+ Post New Thread

Similar Threads

  1. [SIMS] SIMS AM7 Template Change resultset script
    By vikpaw in forum MIS Systems
    Replies: 71
    Last Post: 25th October 2011, 10:38 AM
  2. [SIMS] Corrupt SIMS profiles templates
    By Sylv3r in forum MIS Systems
    Replies: 16
    Last Post: 24th May 2011, 09:25 AM
  3. [SIMS] SIMS Profiles - what happens when staff share a class?
    By ctaylor in forum MIS Systems
    Replies: 2
    Last Post: 19th May 2011, 10:24 PM
  4. Replies: 3
    Last Post: 5th May 2011, 11:26 AM
  5. [SIMS] SIMS profiles
    By rh91uk in forum MIS Systems
    Replies: 5
    Last Post: 29th October 2010, 11:42 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts