none
how-to question about content controls in Word 2013 RRS feed

  • Question

  • Hi,

    I used content controls to insert date fields in two successive columns.  

    Is there a way to total the difference in dates between the fields and have that amount auto-populate in a third column?

    In excel, I would simply enter the following in C1: =(B1-A1)

    Any ideas?

    Monday, April 29, 2013 2:35 PM

Answers

  • In that case, you will need a different approach. The simplest alternative approach is along the lines of:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    On Error Resume Next
    With ContentControl
      If .Type <> wdContentControlDate Then Exit Sub
      With .Range
        If .Information(wdWithInTable) = False Then Exit Sub
        i = ContentControl.Range.Cells(1).RowIndex
        With .Tables(1)
          If Not IsDate(.Cell(i, 1).Range.ContentControls(1).Range.Text) Then Exit Sub
          If Not IsDate(.Cell(i, 2).Range.ContentControls(1).Range.Text) Then Exit Sub
          .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
        End With
      End With
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, May 5, 2013 1:16 PM
  • I wasn't sure which way around your dates were. You can negate the output by changing:
           .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
    to:
           .Cell(i, 3).Range.Text = CStr(-DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
    or:
           .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 1).Range.ContentControls(1).Range.Text, _
            .Cell(i, 2).Range.ContentControls(1).Range.Text))


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, May 7, 2013 8:04 AM
  • Except that you didn't adjust the first two instances, it should work. Try:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    On Error Resume Next
    With ContentControl
      If .Type <> wdContentControlDate Then Exit Sub
      With .Range
        If .Information(wdWithInTable) = False Then Exit Sub
        i = ContentControl.Range.Cells(1).RowIndex
        With .Tables(1)
          If Not IsDate(.Cell(i, 2).Range.ContentControls(1).Range.Text) Then Exit Sub
          If Not IsDate(.Cell(i, 3).Range.ContentControls(1).Range.Text) Then Exit Sub
          .Cell(i, 4).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 3).Range.ContentControls(1).Range.Text))
        End With
      End With
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, May 26, 2013 9:34 AM

All replies

  • The only practical way would be to use a ContentControlOnExit macro. Although date differences can be calculated via field coding, you would still need the ContentControlOnExit to trigger an update (unless you don't mind waiting until the document is previewed/printed or the fields are manually updated). A suitably ContentControlOnExit macro might be:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    With ContentControl
      If .Title = "EndDate" Then
        With ActiveDocument
          For i = 1 To .ContentControls.Count
            If .ContentControls(i).Title = "StartDate" Then
              If IsDate(.ContentControls(i).Range.Text) Then
                i = DateDiff("D", .ContentControls(i).Range.Text, ContentControl.Range.Text)
                Exit For
              End If
            End If
          Next
        End With
        Call UpdateBookmark("MyBookmark", Cstr(i))
      End If
    End With
    End Sub

    Sub UpdateBookmark(BmkNm As String, NewTxt As String)
    Dim BmkRng As Range
    With ActiveDocument
      If .Bookmarks.Exists(BmkNm) Then
        Set BmkRng = .Bookmarks(BmkNm).Range
        BmkRng.Text = NewTxt
        .Bookmarks.Add BmkNm, BmkRng
      End If
    End With
    Set BmkRng = Nothing
    End Sub

    With the above, your two content controls would be titled 'StartDate' and 'EndDate', respectively, and the output would go to a bookmark named 'MyBookmark'. If you want an inclusive day count, you could change:
    Call UpdateBookmark("MyBookmark", Cstr(i))
    to:
    Call UpdateBookmark("MyBookmark", Cstr(i + 1))


    Cheers
    Paul Edstein
    [MS MVP - Word]



    • Edited by macropodMVP Tuesday, April 30, 2013 12:54 PM
    Tuesday, April 30, 2013 12:51 PM
  • Thanks for your response.

    2 more questions for you:

    1. You said the output would go to a bookmark.  What do you mean by 'bookmark'?
    2. How do I use this macro?  I have never used macros before.  Can you dumb it down a notch with something like: 1.  click here, 2. click there, etc.  (I have both word 2010 & 2013).

    Looking forward to trying your advice.

    Thursday, May 2, 2013 10:51 AM
  • In the body of your document, where you want the output to go, insert a bookmark named 'MyBookmark'. If you'd prefer to use something more meaningful, do so and change the 'MyBookmark' reference in the macro to your preferred name.

    With your date-picker content controls, give them the titles 'StartDate' and 'EndDate', respectively.

    Next, press Alt-F11 to open Word VBE. To the left, you'll see an entry for your document. Below that is an entry for 'ThisDocument'. Double-click on that, then paste the code into it. Press Alt-F11 again to return to your document.

    The macro should now do as you require whenever you update either of the 'StartDate' and 'EndDate' content controls.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, May 2, 2013 11:03 AM
  • I ran into one issue with the macro, which to honest is my mistake for not mentioning.  I have several rows where I need to total the difference between date fields.  

    For example:  

    C1: =(B1-A1)

    C2: =(B2-A2)

    C3: =(B3-A3)

    etc.

    B/c there is only one bookmark, everything was getting totaled into C1.

    Is it possible to do this for multiple rows of data?


    Sunday, May 5, 2013 12:14 PM
  • In that case, you will need a different approach. The simplest alternative approach is along the lines of:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    On Error Resume Next
    With ContentControl
      If .Type <> wdContentControlDate Then Exit Sub
      With .Range
        If .Information(wdWithInTable) = False Then Exit Sub
        i = ContentControl.Range.Cells(1).RowIndex
        With .Tables(1)
          If Not IsDate(.Cell(i, 1).Range.ContentControls(1).Range.Text) Then Exit Sub
          If Not IsDate(.Cell(i, 2).Range.ContentControls(1).Range.Text) Then Exit Sub
          .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
        End With
      End With
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, May 5, 2013 1:16 PM
  • Okay.  I'd like to try this.  How to i go about using the macro?  Do I need another bookmark?  A detailed how-to, like your response from Thursday, May 02, 2013 11:03 AM would be very helpful.  

    Thanks

    Monday, May 6, 2013 2:21 PM
  • You use the new macro instead of the old one. The new one doesn't require any titling of the content controls or the use of bookmarks. Instead, it relies on the datepicker content controls being in columns A & B of a table that has at least 3 columns. The previous version didn't require either the content controls or the output to be in a table.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, May 6, 2013 10:53 PM
  • ok gotcha.  everything worked except the date total came out negative!  
    Tuesday, May 7, 2013 7:51 AM
  • I wasn't sure which way around your dates were. You can negate the output by changing:
           .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
    to:
           .Cell(i, 3).Range.Text = CStr(-DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 1).Range.ContentControls(1).Range.Text))
    or:
           .Cell(i, 3).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 1).Range.ContentControls(1).Range.Text, _
            .Cell(i, 2).Range.ContentControls(1).Range.Text))


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, May 7, 2013 8:04 AM
  • Hi again,

    The macro was working fine.  Occasionally, it would stop working & I would have to re-enter the macro.  But recently, it stopped working altogether.  I removed the macros by saving it as a .dotx.  I then re-entered the macro & saved it as a .dotm.  Any ideas on how to get this working again?

    Monday, May 20, 2013 9:29 AM
  • If the macro ceases to work, that suggests either:
    a) you have deleted the content controls, which is possible if you haven't checked the 'content control cannot be deleted' property; or
    b) you're adding it to a dotx or docx file. Macros can only be stored in doc, dotm and docm files. If you create a dotm file for use as a template, the macro will be available to, but not stored in, docx files created from it.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, May 20, 2013 8:46 PM
  • I opened the dotm file & I checked the 'content control cannot be deleted' property on all the date picker fields.  Still no luck.  Is there anything else I can do?

    Tuesday, May 21, 2013 8:24 AM
  • Are you sure the macro is in the dotm file and the content controls are in the first two columns of a three-column table? As you noted, the macro was working fine, so whatever made it stop working is likely to have been a change you've made to the document/template (or the macro itself).

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, May 21, 2013 9:20 AM
  • you are so right.  I added in a column to the left, so the two date picker fields are in columns 2 & 3 now and the column which has the totaled data is in the 4th!  I did realize adding a column would affect the macro.  (fyi - the table has several other columns).  Can you tell what I need to change in the macro?

    Thursday, May 23, 2013 3:25 PM
  • All you need to do is to increment the numbers in the .Cell(i, #) expressions by 1.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, May 23, 2013 11:16 PM
  • Ok.  I increased the numbers, but it didn't work.  Here is what I entered, is there a mistake?

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    On Error Resume Next
    With ContentControl
      If .Type <> wdContentControlDate Then Exit Sub
      With .Range
        If .Information(wdWithInTable) = False Then Exit Sub
        i = ContentControl.Range.Cells(1).RowIndex
        With .Tables(1)
          If Not IsDate(.Cell(i, 1).Range.ContentControls(1).Range.Text) Then Exit Sub
          If Not IsDate(.Cell(i, 2).Range.ContentControls(1).Range.Text) Then Exit Sub
            .Cell(i, 4).Range.Text = CStr(-DateDiff("D", _
            .Cell(i, 3).Range.ContentControls(1).Range.Text, _
            .Cell(i, 2).Range.ContentControls(1).Range.Text))
        End With
      End With
    End With
    End Sub

    Sunday, May 26, 2013 8:57 AM
  • Except that you didn't adjust the first two instances, it should work. Try:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim i As Long
    On Error Resume Next
    With ContentControl
      If .Type <> wdContentControlDate Then Exit Sub
      With .Range
        If .Information(wdWithInTable) = False Then Exit Sub
        i = ContentControl.Range.Cells(1).RowIndex
        With .Tables(1)
          If Not IsDate(.Cell(i, 2).Range.ContentControls(1).Range.Text) Then Exit Sub
          If Not IsDate(.Cell(i, 3).Range.ContentControls(1).Range.Text) Then Exit Sub
          .Cell(i, 4).Range.Text = CStr(DateDiff("D", _
            .Cell(i, 2).Range.ContentControls(1).Range.Text, _
            .Cell(i, 3).Range.ContentControls(1).Range.Text))
        End With
      End With
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, May 26, 2013 9:34 AM
  • thanks - it works!
    Sunday, May 26, 2013 9:50 AM