Shifted and changed in what way?
Shifted and changed in what way?
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Such as if the matrix changed like if a likelihood and impact was changed to result in a different risk rating.
For example if "Rare" likelihood and "Insignificant" impact will now return the risk rating of "Low" but if suddenly I need to have it return a risk rating of "Medium".
Also is the formula "Vlookup" possible to be implemented in word for a risk assessment document like this or is macro the best way to go?
No changes to the code would be required for that or for colour changes.
Word has no lookup functions of any kind.[/QUOTE]
Oh why would no changes to the code be required for any changes to the table?
Is it already built into the code to adapt should the referenced matrix table to changed?
Ahh may i know which part of the code links the table below to read from the matrix directly?
Oh and what if in the future i may want to add yet another table to read from the matrix above directly(which have the same titles of Likelihood and Impact) , is there a fast way of coding it?
Would it be fine if I could skype you and share my screen if thats okay with you
I cannot thank you enough for all the assistance that you have provided me, thank you so much Paul.
The code between 'Select Case .Title' and 'End Select' retrieves the dropdown indices for the user's choices, as values i and j. The next With … End With turns those into matrix positions via the 'Tbl.Cell(j + 2, 9 - i)' code.
As written, your new table would need its own matrix - with the same layout for the first 8 rows, though (even with the existing matrix) you could use more or fewer impact & likelihood rows & columns - and even change what's in each cell (the '9 - i' would need adjustment to suit any change in the rows).
That would be contrary to the intent of the rules here: http://www.vbaexpress.com/forum/faq....aq_req_help_pm
Cheers
Paul Edstein
[Fmr MS MVP - Word]
So if the tables are in different sections as shown in the attached document we will have to re-code the code for that section after i add in another matrix in that new table itself?
SECTIONCandG.docm
Your table in Section G has an entirely different construction from your table in Section C so, amongst other things, the code would need to test which table it's being run from and branch to code applicable to that table.
FWIW, replication of the matrix could be avoided by bookmarking the table in Section C and referring to that table's matrix via the bookmark. This is much closer to how the code in the link I originally posted works. Had you said from the outset you wanted to use the matrix in multiple tables, I could have coded it that way from the outset.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Neither. Try the attached.
I've played around with the risk matrix a bit, just for demo purposes, but that's of no consequence for the macro's execution or the document's functioning. What does matter is that I've applied a bookmark named 'RiskMatrix' to the first table and I've titled the 'Likelihood' and 'Impact' content controls in the second table appropriately.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Select Case .Title simply retrieves the content control's title.
Case "Likelihood" and Case "Impact" tell the code what to do if the title is 'Likelihood' or 'Impact', as the case may be. In essence the code below each retrieves the selection of the corresponding 'Impact' & 'Likelihood' controls, as appropriate, plus the selection of the active content control itself. Those bits of information are necessary for doing the risk matrix lookup. To find out which items in each pair of dropdown lists is referenced, it is necessary to loop through all the options until the selected one is found; which is what the code does.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
They are row (r) and column (c) references. So, 'c + 1' says to look in the next column and 'c - 1' says to look in the previous column.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
ahh how about the (j + 2, 9 - i) in Else .Range.Text = Split(Rng.Tables(1).Cell(j + 2, 9 - i).Range.Text, vbCr)(0)
.Range.Font.ColorIndex = Rng.Tables(1).Cell(j + 2, 9 - i).Range.Font.ColorIndex
.Shading.BackgroundPatternColor = Rng.Tables(1).Cell(j + 2, 9 - i).Shading.BackgroundPatternColor
End If
Same deal - j & i are row & column references.
Cheers
Paul Edstein
[Fmr MS MVP - Word]