#1
|
|||
|
|||
Word Document_ContentControlOnExit Macro to Update a Table
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 |
#2
|
||||
|
||||
Can you post the document with the code and the tables?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
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
|
#4
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Quote:
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 thanks! sorry i missed that button Last edited by illwill; 04-15-2019 at 04:43 AM. |
#7
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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
|
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
You can always put charts, tables, other objects inside rich text CCs and reference them as I've done in the attached revision.
|
#12
|
|||
|
|||
thank you for your effort, will try it as soon i get back from traveling tomorrow evening
|
#13
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#14
|
|||
|
|||
Roger that!
|
#15
|
|||
|
|||
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 |