#1
|
|||
|
|||
Risk Matrix table using Content Controls
Hello All,
I am an Adventurous Activity leader within Scouts Australia and regularly use a 5 x 5 risk matrix when running adventurous activities like Crate Stacking, Rock Climbing, Caving, etc. Currently I am using an Excel version as my template and whilst the Excel version is good it does not always print very well in terms of splitting rows between headers and footers. I was doing a web search and came across Greg Maxey’s ‘Content Controls In Math Applications’ (https://gregmaxey.com/word_tip_pages...perations.html) and this made me wonder if anyone had tried to reproduce a 5 x 5 matrix using Content Controls. In the Excel Risk Management Plan picture, I use data validation in columns D and E to assess the existing / initial risk. An Index and Match formula in column F then returns the risk rating from the Matrix Table. After the controls have been implemented the residual risk is again reassessed using columns H and I, using the same criteria with the final risk rating in column J. I am curious if it is possible to create the Excel layout as a Word version of the 5 x 5 risk management plan using Word Content Controls ??? As new table rows are created would the Content Controls be reproduced on each new row ??? Regards, Dave T |
#2
|
||||
|
||||
This would be far easier to answer if you attached an actual Word document to a post, containing the tables you propose to use. As for conditionally adding a row to a table, see, for example:
https://www.msofficeforums.com/word-...html#post87989 https://www.msofficeforums.com/word-...html#post38461
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hello Paul,
Thanks for the reply and the links. Attached is a Word version of the template (minus one column). I have added the content controls to the Likelihood and Consequences columns, but the 25 combinations of calculations for the Risk Rating columns has me a bit stumped. Regards, Dave T |
#4
|
||||
|
||||
You still don't have your risk matrix table in the document, though. You could create that via a simple copy/paste from Excel.
See also: https://www.msofficeforums.com/word-...tml#post125708
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hello Paul,
Attached is my preferred version, but I do not know if having the matrix in the footer of the first page only is a problem. If the footer table is too complicated I have another version with a matrix within the body of the document. Regards, David |
#6
|
||||
|
||||
Try the attached. I needed to make some minor changes to your table (including restoration of the placeholder texts), but that won't affect its utility.
Note: If you want to shade the output cells to match the ratings, you could change: Code:
.Range.Tables(1).Cell(r, c).Range.Text = StrTxt Code:
With .Range.Tables(1).Cell(r, c) .Range.Text = StrTxt .Shading.BackgroundPatternColor = Tbl.Cell(i + 1, j + 1).Shading.BackgroundPatternColor End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hello Paul,
I really do appreciate all of the effort you have gone into to get this working for me. I cannot thank you enough. Yes the placeholder text is an interesting one... Previously I have done a workaround by having no text and just using a single space, but this did not work in this case. I also found a suggestion online to modify the Placeholder style and set its Font style to 'Hidden' but this also did not appear to do anything either. Once again thank you very much. Regards, Dave T |
#8
|
|||
|
|||
Hello Paul,
Just one more question... I have further completed the form and have added a table cell that should return the highest residual risk from the last column (Risk (ii) i.e. column 9). I have this working in Excel using the following formula: =IF(COUNTIF($J$11:$J20,"High")>0,"High",IF(COUNTIF ($J$11:$J20,"Significant")>0,"Significant",IF(COUN TIF($J$11:$J20,"Moderate")>0,"Moderate",IF(COUNTIF ($J$11:$J20,"Low")>0,"Low","")))) I know the range in the formula above would need to manually adjusted depending upon the number of rows used or I could use a dynamic named range, but how can this be done in Word using content controls for an unknown number of rows ? Regards, Dave T |
#9
|
||||
|
||||
Word has nothing like Excel's COUNTIF function, but it isn't needed anyway. The code to implement the additional output is fairly straightforward.
First, define a new variable: Code:
Dim ArrRisks: ArrRisks = Array(" ", "Low", "Moderate", "Significant", "High") Code:
If i = 1 Or j = 1 Then StrTxt = "" Else StrTxt = Split(Tbl.Cell(i + 1, j + 1).Range.Text, vbCr)(0) End If Code:
If i = 1 Or j = 1 Then StrTxt = " " Else StrTxt = Split(Tbl.Cell(i + 1, j + 1).Range.Text, vbCr)(0) End If For i = 0 To UBound(ArrRisks) If ArrRisks(i) = StrTxt Then Exit For Next With ActiveDocument.SelectContentControlsByTitle("Highest Residual Risk")(1).Range For j = 0 To UBound(ArrRisks) If ArrRisks(j) = .Text Then Exit For Next If i > j Then .Text = StrTxt End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
insert the 2 content controls into a Repeating Section in a table | smaccharoli | Word VBA | 2 | 02-27-2018 08:49 AM |
Duplicating one or more table rows or an entire table with content controls | kevinbradley57 | Word VBA | 10 | 08-17-2017 02:13 PM |
2nd matrix gives a resut that doesn´t match numbers in the 1st matrix | Diver | Excel | 25 | 02-22-2017 04:39 PM |
Macro to save docx to doc that checks compatibility and converts content controls to static content. | staicumihai | Word VBA | 4 | 10-12-2016 08:23 PM |
Content Controls - Add Table Rows | dgiromini | Word VBA | 1 | 04-11-2014 03:04 PM |