Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 41

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

  1. #21
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Shifted and changed in what way?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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

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

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

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


    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?

  6. #26
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Zaxest View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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

  8. #28
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Zaxest View Post
    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.
    Quote Originally Posted by Zaxest View Post
    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).
    Quote Originally Posted by Zaxest View Post
    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....aq_req_help_pm
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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

  10. #30
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

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

  12. #32
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Zaxest View Post
    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.
    Attached Files Attached Files
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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

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

  15. #35
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

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

  17. #37
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

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

  19. #39
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Same deal - j & i are row & column references.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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

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
  •