Word Document_ContentControlOnExit Macro to Update a Table

illwill · #1 04-13-2019, 12:04 AM

new to vba macros , im trying to create a dropmenu that does a few different things, depending on the choice it'll convert the color of the severity then itll tally all the dropemnus and itll update a table thats attached to a graph. so far I have the colors working with the code below

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
With ContentControl.Range
    If ContentControl.Title = "Severity" Then
        Select Case .Text
            Case "Critical"
                .Cells(1).Shading.BackgroundPatternColor = wdColorDarkRed
                .Font.Color = wdColorWhite
                .Font.Bold = True
            Case "High"
                .Cells(1).Shading.BackgroundPatternColor = wdColorRed
                .Font.Color = wdColorBlack
                .Font.Bold = True
            Case "Medium"
                .Cells(1).Shading.BackgroundPatternColor = wdColorOrange
                .Font.Color = wdColorBlack
                .Font.Bold = True
            Case "Low"
                .Cells(1).Shading.BackgroundPatternColor = wdColorYellow
                .Font.Color = wdColorBlack
                .Font.Bold = True
            Case "Informational"
                .Cells(1).Shading.BackgroundPatternColor = wdColorLightBlue
                .Font.Color = wdColorBlack
                .Font.Bold = True
            Case Else
                .Cells(1).Shading.BackgroundPatternColor = wdColorAutomatic
        End Select
    End If
End With
End Sub

The chart and table look like this

if i edit the graph data it'll look like this

i think the code to update the table should look like something like this

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim Total As Long, i As Long
Total = 0
With Selection.Rows(1)
    For i = 1 To 6
        Select Case .Cells(i).Range.ContentControls(1).Range.Text
            Case "Critical"
                Critical = Critical + 1
                Total = Total + 1
            Case "High"
                High = High + 1
                Total = Total + 1
            Case "Medium"
                Medium = Medium + 1
                Total = Total + 1
            Case "Low"
                Low = Low + 1
                Total = Total + 1
            Case "Informational"
                Informational = Informational + 1
                Total = Total + 1
        End Select
    Next i
    .Cells(8).Range.Text = Total
End With
End Sub

the issue I have is, how do i reference that table in the code? second, how do i add both the color changing code and the table update code together so when someone picks an item it updates the table and changes the color, and third, if someone picks and item from the drop down, then decides they want a different choice, how can it update the table to remove the first choice and add the second choice. or should I make this a separate macro that just strictly adds the menu choice at the end , then updates the table and graph

gmayor · #2 04-13-2019, 08:53 PM

Can you post the document with the code and the tables?

illwill · #3 04-14-2019, 01:29 PM

i uploaded here https://ufile.io/d85xfciu should be there for at least 30 days, not sure if theres another option to post to forum, be aware it is a .docm because of the cell/font color changing code embedded in it

gmayor · #4 04-14-2019, 10:28 PM

See the attached which should do what you require, however using a repeating section content control to duplicate the table doesn't appear to produce the desired results, so I would suggest using a button either on the document (as in the attached) or on the ribbon to add tables as required.

The process recalculates the totals after each change so the totals should remain valid if the user changes a control.

macropod · #5 04-15-2019, 01:38 AM

Quote:

Originally Posted by illwill

i uploaded here https://ufile.io/d85xfciu

Instead of directing users to files hosted elsewhere, please attach them to the actual post. You do so via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen. In this case, I've done that for you.

illwill · #6 04-15-2019, 02:14 AM

Quote:

Originally Posted by gmayor

See the attached which should do what you require, however using a repeating section content control to duplicate the table doesn't appear to produce the desired results, so I would suggest using a button either on the document (as in the attached) or on the ribbon to add tables as required.

The process recalculates the totals after each change so the totals should remain valid if the user changes a control.

awesome! the table update works well in your example,
only issue is 'critical' doesnt show in the chart for some reason not sure why
other times
if i copy/paste all of this into another doc and press the "add a Table" button, it pastes all the way at the end of document, is there any way to send it to a certain spot in the document? I have a tables of contents reference called 'Vulnerability Details' that the button should add under

also trying to figure out how to copy the button from your test doc properly, sometimes when i copy/paste it i get a reference errors
"the requested member of the collection does not exist" for : Set oCell = oTable.Cell(5, 2)
i need to move the chart and table to a different part of the doc than the table created

if i were to put the button in a ribbon how would i go about doing that? otherwise the button prints out on the page, or is there a way to prevent the button from printing

Quote:

Originally Posted by macropod

Instead of directing users to files hosted elsewhere, please attach them to the actual post. You do so via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen. In this case, I've done that for you.

thanks! sorry i missed that button

gmayor · #7 04-15-2019, 05:38 AM

I didn't touch your chart. It is exactly as it was in your original. I only showed how to write to your Excel table that serves the chart and also to the table in the document.

You can insert a button using the activeX button option on the Developer tab and apply the code to that button. However given your comments about the button printing, a ribbon button would be preferable see attached and also see http://gregmaxey.mvps.org/word_tip_p...bbon_main.html

You can put the new table anywhere you want by setting a range to that location In the example it simply puts it at the end.

If you move the tables around then they no longer have the same IDs. oTable in question is the copy of Table 2 added to the range oRng. If Table 2 is no longer the correct table e.g. because you have moded table 1, then the code will not work without changing the table IDs as appropriate.

gmaxey · #8 04-15-2019, 07:10 AM

Graham,

Been tinkering on the sidelines this morning. See if the attached addresses the
"...however using a repeating section content control to duplicate the table doesn't appear to produce the desired results" issues you mentioned. It seems to work here.

I added content controls in the fill in table. When a new table is added, the CCs reset to placeholder text and the first CC is selected.

Edit: The "Add Table" command button is now an "Add Table" control on the QAT.

illwill · #9 04-15-2019, 11:02 AM

that final one seems to work when copy paste it in a new document and edit etc. ill test it out and see if theres any issues but i think we might be golden, gentlemen thank you for your help so far, you've saved me days of working trying to figure this out

illwill · #10 04-15-2019, 11:51 AM

crap maybe i spoke to soon, i still had the old vba code in the document i was pasting into, i tried copying the code to the new document, but i think the table references are off, i dont know where to find the table names in the document to rename them to the referenced names.

also theres a reference to the old button I created. i dont know where to find it to delete it from the 'Object' dropdown menu in vba

gmaxey · #11 04-15-2019, 05:36 PM

You can always put charts, tables, other objects inside rich text CCs and reference them as I've done in the attached revision.

illwill · #12 04-15-2019, 08:27 PM

thank you for your effort, will try it as soon i get back from traveling tomorrow evening

gmayor · #13 04-15-2019, 08:40 PM

Quote:

Originally Posted by gmaxey

"...however using a repeating section content control to duplicate the table doesn't appear to produce the desired results" issues you mentioned. It seems to work here.

I found that using the original document, the second table would repeat the selection in the first, so I felt it quicker to do it in a way that I knew would work that the OP could adapt, rather than spend my time trying to resolve the the original setup. Better the devil you know.

gmaxey · #14 04-16-2019, 05:18 PM

Roger that!

illwill · #15 04-19-2019, 07:11 PM

everything worked well in the 2 documents i tested with. thanks again. probably my only time ever needing this

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word Macro to update the layout of document	joaoossilva	Word VBA	4	11-07-2016 01:11 PM
How can I use a Macro to automatically update all the pictures in a word doc	GezLundy	Word VBA	3	06-12-2014 03:23 AM
Document_ContentControlOnExit	Catty	Word VBA	2	11-29-2013 04:49 AM
Update Word table based on another table input	mpdsal	Word VBA	10	10-29-2012 07:40 AM
How do you update existing Outlook calendar item from Word with macro?	Joe Patrick	Word VBA	0	07-09-2011 05:32 AM