SUM values of drop down lists in Word 2010

[SBucki]

Hi,

I don't even know if it is a macro solution I am looking for. However when googling a solution marco's kept coming up, problem was I don't understand them well enough to interpret!

Basically I have a table in word 2010. I have used the Developer tab to add a "Drop Down List Content Control" consisting of 3 options:
Good
Satisfactory
Needs Improvement

They have corresponding values of:
5
0
-5

The table consists of multiple rows, each with the drop down in the last column. On the bottom row I want to sum up all the values of the selected options. Eventually, depending on the task of the row, I'll add different values to the same 3 options, but for now they are all the same. The completed table will then be added to a quick parts form.

Hoping someone can help with this, thanks in advance. Shelley

[macropod]

You could use a ContentControlOnExit macro coded as:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, j As Long, k As Long
If ContentControl.Range.Information(wdWithInTable) = False Then Exit Sub
If ActiveDocument.Range(0, ContentControl.Range.Start).Tables.Count <> 1 Then Exit Sub
With ContentControl.Range.Tables(1)
  For i = 3 To .Rows.Count - 1
    With .Cell(i, 4).Range.ContentControls(1)
      For j = 2 To .DropdownListEntries.Count
        If .DropdownListEntries(j).Text = .Range.Text Then
          k = k + .DropdownListEntries(j).Value
        End If
      Next
    End With
  Next
  .Cell(.Range.Rows.Count, 4).Range.Text = k
End With
End Sub

[gmaxey]

You can also do this with a pseudo on change event. The following code assumes the element (things you are grading) are dropdown CCs in column 2 of a table starting with the first row and the plain text "Score" CC is in column 2 or the last row:

First map the CCs and create a CustomXMLPart
[CODE][Sub MapTheCCs()
Dim oXMLPart As CustomXMLPart
Dim oTbl As Word.Table
Dim lngIndex
Dim strXML As String
'Assumes the element CCs (the things you are grading) are in column 2 of the table starting with row 1.
'and the score content control is in column 2 of the last row.
With ActiveDocument
Set oTbl = .Tables(1)
strXML = "<?xml version='1.0'?><CC_Map xmlns='http://Greg_Maxey/CC_Mapping_Part'>"
For lngIndex = 1 To oTbl.Rows.Count - 1
strXML = strXML & "<Element></Element>"
Next lngIndex
strXML = strXML & "<Score></Score></CC_Map>"
Set oXMLPart = .CustomXMLParts.Add(strXML)
For lngIndex = 1 To oTbl.Rows.Count - 1
oTbl.Cell(lngIndex, 2).Range.ContentControls(1).XMLMapping.SetMapping ("/ns0:CC_Map[1]/ns0:Element[" & lngIndex & "]")
Next lngIndex
oTbl.Cell(oTbl.Rows.Count, 2).Range.ContentControls(1).XMLMapping.SetMapping ("/ns0:CC_Map[1]/ns0:Score[1]")
End With
lbl_Exit:
Exit Sub
End Sub
/CODE]

Then monitor/act on the event:

[CODE][Private Sub Document_ContentControlBeforeContentUpdate(ByVal ContentControl As ContentControl, _
Content As String)
Dim oXMLPart As CustomXMLPart
Dim oNode As CustomXMLNode
Dim lngScore As Long
If ContentControl.Title = "Score" Then Exit Sub
Set oXMLPart = ActiveDocument.CustomXMLParts. _
SelectByNamespace("http://Greg_Maxey/CC_Mapping_Part").Item(1)
With oXMLPart
For Each oNode In .DocumentElement.ChildNodes
If InStr(oNode.BaseName, "Element") = 1 Then
lngScore = lngScore + CLng(oNode.Text)
End If
Next oNode
Set oNode = .SelectSingleNode("/ns0:CC_Map[1]/ns0:Score[1]")
oNode.Text = lngScore
End With
lbl_Exit:
Exit Sub
End Sub/CODE]

[SBucki]

I'm sorry, but I have no idea what either answer means. Can I just copy and paste them into the macro part? I've just tried that with macrpod's answer and it appears to be working. Could you explain what it all means so I know how to adjust if need be? The actual document has many more columns and and is comprised of multiple "quick parts" would the code work across them all? Thank you.

[macropod]

Can I just copy and paste them into the macro part?

ContentControlOnExit macro's will only work if their added to the document's 'ThisDocument' code module.

Could you explain what it all means so I know how to adjust if need be?

The macro I provided is tailored to the example you posted. Although it can work with any number of rows, it is coded to work only with the first table in the document.
This line:
If ContentControl.Range.Information(wdWithInTable) = False Then Exit Sub
Checks whether the Content Control being exited is in a table. If not, the macro terminates.
Having established that the Content Control is in a table, this line:
If ActiveDocument.Range(0, ContentControl.Range.Start).Tables.Count <> 1 Then Exit Sub
Checks whether the Content Control being exited is the first table. If not, the macro terminates.
Having established that the Content Control is in a table, this code:

        For i = 3 To .Rows.Count - 1 
            With .Cell(i, 4).Range.ContentControls(1) 
                For j = 2 To .DropdownListEntries.Count 
                    If .DropdownListEntries(j).Text = .Range.Text Then 
                        k = k + .DropdownListEntries(j).Value 
                    End If 
                Next 
            End With 
        Next

processes every row of the first table, from row 3 to 1 less than the row count (i.e. For i = 3 To .Rows.Count - 1). On each row, it looks at the Content Control in the 4th column (i.e. With .Cell(i, 4).Range.ContentControls(1)). It then uses another loop to compare the displayed text against the dropdown list entries and, when a match is found, extracts & tallies the corresponding value (i.e. k = k + .DropdownListEntries(j).Value). When the tallying is done, the result is output to column 4 on the last row (i.e. .Cell(.Range.Rows.Count, 4).Range.Text = k).

The actual document has many more columns and and is comprised of multiple "quick parts" would the code work across them all?

That's ambiguous. The macro works with content controls in a specified table, regardless of document columns. As coded, the macro works only with the first table. If you wanted one to work with multiple tables, you should have specified that and provided some representative data. The changes need to work with multiple tables might be trivial, or they might require a complete re-write.

[SBucki]

Thank you for the explanation. I think I understand it, but I would not be able to tweak if need be in the future! I believe you are right about the complexity of the the document. I thought I was making it easier by uploading a simple version, but clearly this was the wrong way to go about it.

I can't attach the actual form as it says it is an invalid file. (I think it maybe because it is a template?) Do you have an email address I could send it to please?

The 3 quick parts drop downs which have values that require summing are titled;
Bulk ID
PM & Air Monitoring
Surveying

Thank you for your help so far, I am a bit concerned this might get too complicated with the various drop downs and tables.

[gmaxey]

What do you mean by "3 quick part drop downs." What is a "quick part dropdown?" Paul's solution is robust and works well. The only drawback is that the calculation/update will not occur unless the user exits the dropdown control after making the selection. Since you can't guarantee a cooperative document user you are left with the possibility of an inaccurate result or writing more code to ensure the calculation/update occurs before printing or saving.


The method I suggested, while seemingly more complex, will update automatically when the user makes a new selection.

I put a working example (employee evaluation) in a public dropbox: https://dl.dropboxusercontent.com/u/...xample%29.docm

[SBucki]

I've saved it as a normal doc, not a template and that is worked. Audit Draft.docx

Quick parts are form/set options you can create to allow quicker entry. In this doc I've created them to allow the front cover to stay the same, but for different tables to be selected depending upon what type of audit you are conducting. 3 of the audit types have dropdown controls to calculate.

Your working example is very impressive, but I don't know if it would work for all the tables in my doc?

[macropod]

Since there are no content controls in your attachment, it remains impossible to say what the code requirements might be. Try attaching a docx file with all the tables & content controls in-situ.

[SBucki]

The content controls are in the custom galleries. At the bottom where it says "Select From Drop Down" if you pick either Bulk ID, PM & Air Monitoring or Surveying you will see the tables with the content controls.

[macropod]

There's nothing there - the Quick parts are all in your Gallery only.

[SBucki]

How frustrating. Don't know why they are not appearing for you. Here are each of the three quick parts as separate documents.

Audit Draft Bulk.docxAudit Draft PM.docxAudit Draft Survey.docx

Have just noticed I have not put total rows int he Bulk one.

[macropod]

Given the structure of your tables, it appears the solution requires a substantial re-working of the code. For one thing, your 'tables' actually comprise multiple logical tables. For another, the tallies go on the second row, not the last.

Try:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False
Dim Tbl As Table, i As Long, j As Long, k As Long, l As Long
If ContentControl.Range.Information(wdWithInTable) = False Then Exit Sub
Set Tbl = ContentControl.Range.Tables(1)
With Tbl
  l = 2
  For i = 3 To .Rows.Count
    If .Rows(i).Cells.Count = 4 Then
      With .Cell(i, 4).Range
        If .ContentControls.Count = 1 Then
          With .ContentControls(1)
            If .Type = wdContentControlDropdownList Then
              For j = 2 To .DropdownListEntries.Count
                If .DropdownListEntries(j).Text = .Range.Text Then
                  k = k + .DropdownListEntries(j).Value
                  Exit For
                End If
              Next
            End If
          End With
        Else
          Tbl.Cell(l, 4).Range.Text = "Score" & Chr(11) & k
          k = 0: l = i
        End If
      End With
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

[SBucki]

It works in the individual versions, but when I try and put it into the version with the quick parts and save as a macro enabled template, the custom galleries stop working!

Am I being over complicated with what I am trying to do? Would it be easier to add another column and when you fill out the control content the value appears in the next column which is then just summed? Or can you not have macro's with quick parts?

Sorry, I really appreciate your help, it just appears that everything I create to make things easier, is much more complicated than expected!

[macropod]

I don't know of anything about the macro, per se, that would interfere with using your custom galleries.

[SBucki]

If I save it as a macro enabled document, not a template it works!!!! And for all the different quick parts. Each one totals. So scared to touch it now!

Thank you so much. Think a course on VBA maybe useful for the future...