Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 41

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

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location

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

    Hi VBA Express community, I currently have a task at hand and i'm not too sure how to solve it.

    Screenshot_1.jpg

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

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: https://answers.microsoft.com/en-us/...a-4a9eaf5fd371
    Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Sorry about that, its my first time posting for help in forums, will keep that in mind! :-)

  5. #5
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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

    Risk Rating Table.docx

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    See attached.
    Attached Files Attached Files
    Last edited by macropod; 03-31-2020 at 09:12 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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.

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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 :-)

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Zaxest View Post
    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'.

    Quote Originally Posted by Zaxest View Post
    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.
    Last edited by macropod; 03-31-2020 at 09:13 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  14. #14
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post

    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?

  15. #15
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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!

  16. #16
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by Zaxest View Post
    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!

  17. #17
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    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?

  18. #18
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  19. #19
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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!

  20. #20
    VBAX Regular
    Joined
    Mar 2020
    Posts
    25
    Location
    Quote Originally Posted by macropod View Post
    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 :-)

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
  •