Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-13-2019, 12:04 AM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default 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
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
Reply With Quote
  #2  
Old 04-13-2019, 08:53 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,706
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Can you post the document with the code and the tables?
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 04-14-2019, 01:29 PM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

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
Attached Files
File Type: docm test.docm (45.1 KB, 0 views)
Reply With Quote
  #4  
Old 04-14-2019, 10:28 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,706
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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.
Attached Files
File Type: docm test.docm (59.2 KB, 5 views)
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 04-15-2019, 01:38 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,223
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by illwill View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 04-15-2019, 02:14 AM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
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 View Post
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

Last edited by illwill; 04-15-2019 at 04:43 AM.
Reply With Quote
  #7  
Old 04-15-2019, 05:38 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,706
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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.
Attached Files
File Type: docm Test Excel embedded chart.docm (59.9 KB, 0 views)
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 04-15-2019, 07:10 AM
gmaxey gmaxey is offline Windows 10 Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 829
gmaxey will become famous soon enough
Default

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.
Attached Files
File Type: docm Adding Data and updating Chart Ver. 1.1.docm (64.1 KB, 1 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 04-15-2019, 11:02 AM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 04-15-2019, 11:51 AM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 04-15-2019, 05:36 PM
gmaxey gmaxey is offline Windows 10 Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 829
gmaxey will become famous soon enough
Default

You can always put charts, tables, other objects inside rich text CCs and reference them as I've done in the attached revision.
Attached Files
File Type: docm Adding Data and updating Chart Ver. 1.2.docm (67.8 KB, 2 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #12  
Old 04-15-2019, 08:27 PM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

thank you for your effort, will try it as soon i get back from traveling tomorrow evening
Reply With Quote
  #13  
Old 04-15-2019, 08:40 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,706
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Quote:
Originally Posted by gmaxey View Post
"...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.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #14  
Old 04-16-2019, 05:18 PM
gmaxey gmaxey is offline Windows 10 Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 829
gmaxey will become famous soon enough
Default

Roger that!
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #15  
Old 04-19-2019, 07:11 PM
illwill illwill is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 7
illwill is on a distinguished road
Default

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

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 09:11 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft