Consulting

Page 1 of 5 1 2 3 ... LastLast
Results 1 to 20 of 81

Thread: How to change Date Picker content control format based on date comparison

  1. #1

    How to change Date Picker content control format based on date comparison

    Good morning,

    I have two date picker content controls ("Visit Date - From" and "Visit Date -To") in a Word template. The template has been set up as a content type for a sharepoint library, so the two date fields are entered via the document information panel. I would like to change the format of the "Visit Date - From" depending on the dates entered in these two fields. The date format should appear as follows:

    • When the month in the 'from date' is different to the month in the 'to date', the format should be for example, 03 March - 12 April 2014, i.e. dd mmmm - dd mmmm yyyy.
    • When the same month appears in the 'from date' and the 'to date', the format should be for example, 03 - 08 March 2014, i.e. dd - dd mmmm yyyy.

    Both content controls in my word template are set to (dd mmmm yyyy).

    I thought I had cracked it by creating a calculated column in my sharepoint library using the formula:=IF(MONTH([Visit Date - From])=MONTH([Visit Date - To]),TEXT([Visit Date - From],"DD"),TEXT([Visit Date - From],"DD MMMM")) only to discover that you cannot insert calculated columns or values into a word document, at least not OOTB.

    Is it possible to achieve this through VBA?

    I managed to compare the two fields, but that's only when the same date appears in both fields (pretty feeble attempt I know).

    Sub SetDateFormat()
    
    If ActiveDocument.SelectContentControlsByTag("Visit_x0020_Date_x0020__x002d__x0020_From").Item(1).Range.Text = _
     ActiveDocument.SelectContentControlsByTag("Visit_x0020_Date_x0020__x002d__x0020_To").Item(1).Range.Text Then
     
    MsgBox "Match"
    End If
    End Sub
    Any pointers would be really appreciated.
    Last edited by JDS_916; 03-12-2014 at 03:23 AM.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Doing this is quite a complex undertaking. See attached. Note the use of content control enter/exit events plus two custom document properties.

    As yet, I haven't coded to validate that the from date is before the to date, or to automatically restore the from month/year, when appropriate, if the user changes the 'To' month or year after first triggering the truncation of the 'To' date. I'll leave you with those little challenges.

    PS: Note the ordinal date numbering - a useful tweak if you like that kind of thing.
    Attached Files Attached Files
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Hi macropod,

    Many thanks for the solution and getting back so quickly. There are two things that happen when I set the dates a second time. The message appears:

    Run-time error '6124':

    You are not allowed to edit this selection because it is protected.

    Debug shows: Text = vbNullString

    With ActiveDocument.CustomDocumentProperties
              Str1 = Format(.Item("ToDt").Value, "MM YYYY")
              Str2 = Format(.Item("FromDt").Value, "MM YYYY")
            End With
            MsgBox Str1 & vbTab & Str2
            If Str1 = Str2 Then
              Set RngTmp = .Range
              With RngTmp
                .Start = .Words.Last.Start - 1
                .Text = vbNullString
              End With
            End If
            .Type = wdContentControlDate
          End With
        End If
        .Type = wdContentControlDate
      End If
    End With
    Done:
    Application.ScreenUpdating = True
    End Sub
    When I reset, the following line is highlighted:

    ActiveDocument.CustomDocumentProperties("ToDt").Value = Format(ContentControl.Range.Text, "MM/DD/YYYY")

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim i As Long, j As String, Rng As Range, RngTmp As Range
    Dim CCtrl As ContentControl, Str1 As String, Str2 As String
    With ContentControl
      If (.Title = "Visit Date - From") Or (.Title = "Visit Date - To") Then
        If .Range.Text = .PlaceholderText Then Exit Sub
        .Type = wdContentControlRichText
        Set Rng = .Range
        If .Title = "Visit Date - From" Then
          ActiveDocument.CustomDocumentProperties("ToDt").Value = Format(ContentControl.Range.Text, "MM/DD/YYYY")
        ElseIf .Title = "Visit Date - To" Then
          ActiveDocument.CustomDocumentProperties("FromDt").Value = Format(ContentControl.Range.Text, "MM/DD/YYYY")
        End If
        For i = 0 To UBound(Split(.Range.Text, " "))
          j = Split(.Range.Text, " ")(i)
    After that I am unable to get the calendar to appear.

    Also, is it possible for the code to run without the message boxes displaying. Many thanks.

    Kind regards,

    JDS_916
    Last edited by JDS_916; 03-12-2014 at 08:59 AM.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I'm not getting any errors in the document I posted. Do you have editing restrictions or content control locking applied? As for the message boxes, they're left over from testing and you can safely delete them from the code.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: http://answers.microsoft.com/en-us/o...b-e1aace36b93e
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    Hi macropod,

    I'm just trying to figure out this run-time error '6124'. In the meantime, would you be able to tell me how to remove the ordinal date numbering as it is not needed at present. Many thanks for your time.

    Kind regards,

    JDS_916

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    To remove the ordinal numbering, delete:
        For i = 0 To UBound(Split(.Range.Text, " "))
          j = Split(.Range.Text, " ")(i)
          If IsNumeric(j) Then
              With Rng
                .Start = .Start + InStr(ContentControl.Range.Text, j) + Len(j) - 1
                .End = .Start
                .InsertAfter Ordinal(Val(j))
                .Font.Superscript = True
                .Start = .Start + 2
                .End = .Start
              End With
            Exit For
          End If
        Next
    and the Ordinal Function.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    It's really strange, I can use both fields for a time and then it will just fail with the error 6124 (Text = vbNullString), usually the To Date field.

    Regards,

    JDS_916

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Sounds like there might be a problem with Word on your system. Try repairing the Office installation (via Programs & Features > Microsoft Office > Change in the Windows Control Panel), then re-starting your system.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    Hi macropod,

    I've tried the code on my laptop and on a few workstations at work (Windows 7, Word 2010) and it fails with the same issues. There are no restrictions or faults with MS Office/MS Word at my end.

    I noticed that when the fields display the same month, i.e. dd - dd mmmm yyyy and you then select a new date in the 'Visit Date - To field which has a different month to that in the 'Visit Date - From' field, the 'Visit Date - From' field does not change to display dd mmmm format.

    Sorry to keep bothering you. My knowledge of VBA extends to basic recorded macros.

    Regards,

    JDS_916
    Last edited by JDS_916; 03-13-2014 at 08:56 AM.

  11. #11
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try replacing all of the code in the document with:
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim Str1 As String, Str2 As String
    With ContentControl
      If (.Title = "Visit Date - From") Or (.Title = "Visit Date - To") Then
        If .Range.Text = .PlaceholderText Then Exit Sub
        If .Title = "Visit Date - From" Then
          .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
          Str1 = .Range.Text
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1)
            .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
            Str2 = .Range.Text
          End With
        Else
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1)
            .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
            Str1 = .Range.Text
          End With
          Str2 = .Range.Text
        End If
        With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1)
          If Format(Str1, "YYYYMMDD") > Format(Str2, "YYYYMMDD") Then
            If (ContentControl.Title = "Visit Date - From") Then
              With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1)
                .Range.Text = ""
              End With
            Else
              .Range.Text = ""
            End If
          ElseIf Str1 = Str2 Then
            .Range.ParentContentControl.DateDisplayFormat = "'on '"
          ElseIf Split(Str1, " ")(2) = Split(Str2, " ")(2) Then
            If Split(Str1, " ")(1) = Split(Str2, " ")(1) Then
              .Range.ParentContentControl.DateDisplayFormat = "D' to '"
            Else
              .Range.ParentContentControl.DateDisplayFormat = "D MMMM' to '"
            End If
          End If
        End With
      End If
    End With
    Application.ScreenUpdating = True
    End Sub
    and deleting the ' to ' between the content controls. With this new approach you can also delete the custom document properties. Furthermore, the new code also handles: both dates being the same; changes in either date at any time; and 'From' dates being later that 'To' dates (by clearing the date in whichever content control isn't being updated).
    Last edited by macropod; 03-13-2014 at 04:27 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  12. #12
    Macropod, it works like a dream. Thank you so much for your expertise and time.

    One last question: The code will be placed in a macro-enabled template, does the code have to be in the document or can it remain in the template?

    Regards,

    JDS_916

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Provided the template is available (e.g. you don't try to use the document at home without re-attaching it to the template there), it should work OK.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  14. #14
    Hi macropod

    I ran into a problem because the data is placed into the content controls on the document via the fields on the document information panel, therefore, the OnExit does not work.

    Regards,

    JDS_916

  15. #15
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    It would have been helpful if you had said how the content controls are being populated at the outset. To a large extent, this little revelation has rendered much of the previous work unnecessary...

    Perhaps you could share exactly how this updating is done, including any code that's used for the process.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  16. #16
    In my first post I stated:

    I have two date picker content controls ("Visit Date - From" and "Visit Date -To") in a Word template. The template has been set up as a content type for a sharepoint library, so the two date fields are entered via the document information panel.
    This is still not really clear, sorry for that. There is no code involved just a case of databinding.

    I found this information:

    The document properties a user enters through the document information panel are stored in specific sections of Open XML Formats files. For documents stored in Microsoft SharePoint Foundation, these property values are promoted back to the document library, as column values, when the user updates them in the document. Similarly, if the user updates the content type column values in the SharePoint Foundation user interface, the new values are demoted into the document itself, as document properties.

    Regards,

    JDS_916
    Last edited by JDS_916; 03-14-2014 at 02:13 PM.

  17. #17
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Sorry, missed that. I don't use SharePoint, so I can't really comment on how that side of things works. However, I'd assume that, since you're using a template, it could use a Document_New macro to trigger the content control updating, something along the lines of:
    Private Sub Document_New()
     Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1), False)
     End Sub
    The only issue I can see is whether the Document_New macro runs before, or after, the data are updated; it needs to run after.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  18. #18
    Clicking on new document within the sharepoint library will open up a new document in Word. This process is just like double-clicking on a template to obtain a document or creating a new document in Word. The document information panel is displayed (in Word) and contains the fields for the document metadata. I have a few text fields, a few dropdown fields and the two date fields. All these fields represent the columns of the document library. The fields are mandatory which means you must enter data before saving the file. I have a macro Sub FileSave() which creates a filename based on some of the fields.

    Regards,

    JDS_916

  19. #19
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    In that case, you could add the line:
    Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle(" Visit Date - To")(1), False)
    to your FileSave macro - before the save.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #20
    Thanks macropod, I'll be trying it first thing Monday morning (no remote access to the sharepoint server at the moment) and reporting the result. I'm sure it will work. Once again many thanks for your time and expertise.

    Regards,

    JDS_916

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •