How do i automatically get a column of a table filled up after having other values

[Zaxest]

Hi VBA Express community, I currently have a task at hand and i'm not too sure how to solve it.:(
https://preview.redd.it/4mwcwj7v6vp41.png?width=694&format=png&auto=webp&19466b3c
26242

I would like to automate the rightmost column on the RISK Ratings based on Likelihood and Impact.
For example, under Risk 1, if i were to click "rare" as the likelihood and "insignificant" as the impact both from their own dropdown lists, i would like the risk rating to be automatically filled as "Low" with reference to the colourful table above.
Another example would be if i chose Rare as Likelihood and Catastrophic as the Impact, i would automatically get back the risk rating of Medium on the rightmost column.
Thank you so so much to whoever may have the answer to help me with this task..

[macropod]

See, for example, post #6 at: https://www.msofficeforums.com/word-vba/39652-risk-matrix-table-using-content-controls.html

[macropod]

Cross-posted at: https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-automatically-get-a-column-of-a-table/338defc7-b083-47d8-943a-4a9eaf5fd371
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[Zaxest]

Sorry about that, its my first time posting for help in forums, will keep that in mind! :-)

[Zaxest]

See, for example, post #6 at: https://www.msofficeforums.com/word-vba/39652-risk-matrix-table-using-content-controls.html

Sorry but I'm unable to understand the code for the document in the referenced post, could I get an explanation based on my document please?
Thank you so much for your time

26244

[macropod]

See attached.

[Zaxest]

See attached.
Thank you so much Paul, would i be able to copy this table directly into another document and it'll work just fine? Or would i have to do what you did onto that document.
If its cool with you, may i have the code or steps that you went through to complete the table for future reference? Thank you so much for your time :D

[macropod]

The code will work on any document that has the same table as the one in my attachment - which is the same as yours except that I've retitled your 'Probablity' content controls to 'Likelihood' for consistency - and I've applied the 'Impact' title to all the content controls in the next column. Note also that the code also provides for the addition of new rows - simply select an impact from the last content control, then exit it and you'll invoke that process.

To see the VBA code that drives the process, press Alt-F11. To make that code available to all documents using this form, save my attachment as a macro-enabled template (i.e. .dotm format). Any new documents created from that template will have access to the macro code and can be saved in the docx format (Word's default). If you send the document to someone else whose network setup doesn't permit access to your template, though, they'll need the template too, so they can save it in their templates folder.

[Zaxest]

The code will work on any document that has the same table as the one in my attachment - which is the same as yours except that I've retitled your 'Probablity' content controls to 'Likelihood' for consistency - and I've applied the 'Impact' title to all the content controls in the next column. Note also that the code also provides for the addition of new rows - simply select an impact from the last content control, then exit it and you'll invoke that process.

To see the VBA code that drives the process, press Alt-F11. To make that code available to all documents using this form, save my attachment as a macro-enabled template (i.e. .dotm format). Any new documents created from that template will have access to the macro code and can be saved in the docx format (Word's default). If you send the document to someone else whose network setup doesn't permit access to your template, though, they'll need the template too, so they can save it in their templates folder.

Hi Paul, thank you so much, may i check, how do i copy the macro over into my full file? I'm unable to send you the full file from which i've extracted the table from as it is a company document.
So basically this risk table is a section from my full file and i would like to replace the original table with the table which you have added a macro to.
Do i just go to alt-f11 and copy your macro code over? Or is there a way that i'm supposed to follow to copy your code from alt-f11 into my full document.
Thank you so much for your time.

[macropod]

It's basically just a matter of copy & paste:
1. Open both documents
2. Press Alt-F11 to open the VBE
3. Copy the code from the 'ThisDocument' module of the document I attached to post 6, then paste it into
the 'ThisDocument' module of your own document.
4. Done.
You don't need to copy the table across, but you do need to use the same Content Control titles.

[Zaxest]

It's basically just a matter of copy & paste:
1. Open both documents
2. Press Alt-F11 to open the VBE
3. Copy the code from the 'ThisDocument' module of the document I attached to post 6, then paste it into
the 'ThisDocument' module of your own document.
4. Done.
You don't need to copy the table across, but you do need to use the same Content Control titles.



Do i copy everything under every dropdown on the right? For example theres the 'ContentControlOnExit' tab and other options which contain code.
May i know how do i change the content control titles? You changed the titles yea.
Thank you so much Paul :-)

[macropod]

Do i copy everything under every dropdown on the right? For example theres the 'ContentControlOnExit' tab and other options which contain code.

You copy everything from'Option Explicit' to 'End Sub'.


May i know how do i change the content control titles?
Click on a given content control and, on the Developer menu, choose 'Properties'. The Content Control Properties dialogue box will the open. Change it there.

[macropod]

I've just realized that, since your document has vertically-merged cells, the code for adding a new row won't work the way I wrote it. I've updated the code in the attachment to post #6.

[Zaxest]

You copy everything from'Option Explicit' to 'End Sub'.


Click on a given content control and, on the Developer menu, choose 'Properties'. The Content Control Properties dialogue box will the open. Change it there.

Thanks Paul, how do i remove the content control dropdown list on the right-most column like you did? I can't seem to to do it, I'm not supposed to remove content control yea?

[Zaxest]

I've just realized that, since your document has vertically-merged cells, the code for adding a new row won't work the way I wrote it. I've updated the code in the attachment to post #6.

Ahh alright thank you so much!

[Zaxest]

Thanks Paul, how do i remove the content control dropdown list on the right-most column like you did? I can't seem to to do it, I'm not supposed to remove content control yea?

Oh nevermind, the drop-down list disappeared after i remembered to rename my content controls.
Thank you so so much for your assistance Paul! :-)
Stay healthy and I hope you'll have a great day ahead!

[Zaxest]

Oh and by the way Paul, what if my organization's network does not permit macro files? They were wrongly classified as malware once i mailed them to my work computer.
Do i covert the Docx to Dotm and then back to Docx before sending it to my work computer?

[macropod]

The whole process collapses if you can't use macros. That said, even Word's standard Normal template is a macro-enabled one, and many users doubtless store macros there. Saving the document as a template obviates the need for the user documents to contain macros and, provided the template folder is a designated Safe one, anyone whose workstation has access to that folder can use the document without ever using a document containing macros.


Saving a Word document in the docx format wipes out any macros in it. Saving the document as a .dotm template, means the docx documents Word creates from it don't need the macro.

[Zaxest]

The whole process collapses if you can't use macros. That said, even Word's standard Normal template is a macro-enabled one, and many users doubtless store macros there. Saving the document as a template obviates the need for the user documents to contain macros and, provided the template folder is a designated Safe one, anyone whose workstation has access to that folder can use the document without ever using a document containing macros.


Saving a Word document in the docx format wipes out any macros in it. Saving the document as a .dotm template, means the docx documents Word creates from it don't need the macro.


Ohh alright. Thanks Paul!

[Zaxest]

See attached.
Oh and by the way Paul, may i know how do i edit the code if the table for the risk matrix were to be shifted and changed?
Thank you for your time :-)

[macropod]

Shifted and changed in what way?

[Zaxest]

Shifted and changed in what way?
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".

[Zaxest]

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?

[macropod]

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".
No changes to the code would be required for that or for colour changes.

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?
Word has no lookup functions of any kind.

[Zaxest]

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?

[macropod]

Oh why would no changes to the code be required for any changes to the table?
Because the code reads the matrix directly. If you look at the code, you'll see there's no mention of the risk ratings or the colours associated with them.

[Zaxest]

Because the code reads the matrix directly. If you look at the code, you'll see there's no mention of the risk ratings or the colours associated with them.

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.

[macropod]

Ahh may i know which part of the code links the table below to read from the matrix directly?
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.

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?
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).

Would it be fine if I could skype you and share my screen if thats okay with you
That would be contrary to the intent of the rules here: http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_req_help_pm

[Zaxest]

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.php?faq=psting_faq_item#faq_req_help_pm

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?
26251

[macropod]

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.

[Zaxest]

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.


Ah... sorry about that Paul, didn't take that into consideration before bringing only 1 section up to you.
Would the coding for a whole new table be tougher or would changing it so that it'll be like the one you originally posted be tougher?
Thanks for your time

[macropod]

Would the coding for a whole new table be tougher or would changing it so that it'll be like the one you originally posted be tougher?

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.

[Zaxest]

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.


oo okies thank you so much Paul! I'll go take a look at it.

[Zaxest]

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.


Hi Paul, i'm not quite sure on how to understand the "Select" portion of the code, are you able to explain briefly what each line does?
thank you so much !

[macropod]

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.

[Zaxest]

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.

may i know the logic behind the "r,c +1" in ""With .Range.Tables(1).Cell(r, c + 1).Range.ContentControls(1)""?
and the corresponding r,c-1 in the impact content control in the code "" With .Range.Tables(1).Cell(r, c - 1).Range.ContentControls(1)""

[macropod]

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.

[Zaxest]

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.

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

[macropod]

Same deal - j & i are row & column references.

[Zaxest]

Same deal - j & i are row & column references.

ahh okies thank you, because i'm still not too sure how to modify the code if the risk matrix shifts or changes in the future

[macropod]

If it does, you most likely won't need to modify anything other than the code in post #38, and possibly not even then.

As demonstrated in my last attachment, changes to what's in the matrix don't require any code changes. Likewise, it doesn't matter if:
• you add or delete rows (provided the 'Likelihood' content controls are updated to match); or
• where you move the matrix to, provided you move the 'RiskMatrix' bookmark with it.