Consulting

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 41 to 60 of 81

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

  1. #41
    Yes true. It's the method used to link the two sets of content controls. I got it from the information you provided in the attached link.

    Simple Linked Content Controls

    While not actually mapped, the following quick and simple method was suggested by Aaron Marin via user feedback on this page:

    • Insert a plain text content control.
    • Select the control (by clicking the small control tab)then Developer>ontrols>Properties and define a title for the control.
    • Right click and select "Copy."
    • Position the cursor where you need the text repeated.
    • Using the Home tab click Paste>Paste Special and select the "Paste link" option button. Then in the "As" dropdown select "Unformatted Text" and click "OK."
    • Now anytime the text is changed in the initial control and you exit the control all of the linked copies will update
    This is where it creates ole_links and in doing so a warning message is displayed each time the document is opened.

    What could work is giving the two controls on the title page unique style names and then use the 'StyleRef' field on the introduction page. As the controls are all on one line on the title page, i.e. Visit Dates: 'Visit Date - From' - 'Visit Date - To', I could use style separators.
    Attached Images Attached Images
    Last edited by JDS_916; 03-24-2014 at 05:03 PM.

  2. #42
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Here's a different approach:
    1. Make your subsidiary date content controls plain text or rich text.
    2. Give those content controls the same "Visit Date - From" and "Visit Date - To" titles as the date pickers.
    3. Add a new variable to the code: i As Long
    4. Add the following code after the code following the 'Else' in the final If ... End If block.
                    With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
                      For i = 2 To .Count
                        .Item(i).LockContents = False
                        .Item(i).Range.Text = .Item(1).Range.Text
                        .Item(i).LockContents = True
                      Next
                    End With
                    With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
                      For i = 2 To .Count
                        .Item(i).LockContents = False
                        .Item(i).Range.Text = .Item(1).Range.Text
                        .Item(i).LockContents = True
                      Next
    Note: With these changes, the subsidiary content controls are non-editable; they merely replicate the content in the main ones.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #43
    I guess that it's in...

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

    ElseIf Format(Str1, "YYYYMMDD") = Format(Str2, "YYYYMMDD") Then                ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1).Range.Text = ""
                    ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1).Range.Text = ""
                    MsgBox "'Visit Date - From' is the same as 'Visit Date - To'" & _
                    vbCr & vbTab & "Please re-input the correct dates", vbCritical
                Else
                    With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1)
                        If Split(Str1, " ")(2) = Split(Str2, " ")(2) Then
                            If Split(Str1, " ")(1) = Split(Str2, " ")(1) Then
                                .Range.ParentContentControl.DateDisplayFormat = "D"
                                '.Range.ParentContentControl.DateDisplayFormat = "D' - '"
                            Else
                                .Range.ParentContentControl.DateDisplayFormat = "D MMMM"
                                '.Range.ParentContentControl.DateDisplayFormat = "D MMMM' - '"
                            
                            
                            HERE
                            
                            
                            End If
                        End If
                    End With
                End If
            End If
        End With
        Application.ScreenUpdating = True
    End Sub

    Or am I in the wrong macro!

  4. #44
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    No, here:
    Else 
        With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1) 
            If Split(Str1, " ")(2) = Split(Str2, " ")(2) Then 
                If Split(Str1, " ")(1) = Split(Str2, " ")(1) Then 
                    .Range.ParentContentControl.DateDisplayFormat = "D" 
                     '.Range.ParentContentControl.DateDisplayFormat = "D' - '"
                Else 
                    .Range.ParentContentControl.DateDisplayFormat = "D MMMM" 
                     '.Range.ParentContentControl.DateDisplayFormat = "D MMMM' - '"
                End If 
            End If 
        End With
    ' *** HERE *** 
    End If 
    End If 
    End With 
    Application.ScreenUpdating = True 
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #45
    I receive an error when I place the code where stated:

    End If
                        End If
                    End With
                      
                    With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
                        For i = 2 To .Count
                                .Item(i).LockContents = False
                                .Item(i).Range.Text = .Item(1).Range.Text
                                .Item(i).LockContents = True
                       Next
                    
                      End With
                    With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
                        For i = 2 To .Count
                                .Item(i).LockContents = False
                                .Item(i).Range.Text = .Item(1).Range.Text
                                .Item(i).LockContents = True
                       Next
                           
                End If 'IT HIGHLIGHTS THIS 'EndIf' AND DISPLAYS compile error: End if without block if.
            End If
         End With
        Application.ScreenUpdating = True
    End Sub
    Regards,

    JDS_916

  6. #46
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Sorry, when I copy/pasted the code for post #42, I missed the closing 'End With' statement
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #47
    Run-time error '6202':

    This property can only be used with date control controls.

    It highlights: [CODE][.Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"/CODE]

    Regards,

    JDS_916

  8. #48
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try this code:
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
        Application.ScreenUpdating = False
        Dim Str1 As String, Str2 As String, i As Long
        With ContentControl
            If (.Title = "Visit Date - From") Or (.Title = "Visit Date - To") Then
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        If .Range.Text = .PlaceholderText Then Exit Sub
                        .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
                        Str1 = .Range.Text
                        Exit For
                      End If
                    End With
                  Next
                End With
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        If .Range.Text = .PlaceholderText Then Exit Sub
                        .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
                        Str2 = .Range.Text
                        Exit For
                      End If
                    End With
                  Next
                End With
              End If
              If Format(Str1, "YYYYMMDD") >= Format(Str2, "YYYYMMDD") Then
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        .Range.Text = ""
                      Else
                        .LockContents = False
                        .Range.Text = ""
                        .LockContents = True
                      End If
                    End With
                  Next
                End With
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        .Range.Text = ""
                      Else
                        .LockContents = False
                        .Range.Text = ""
                        .LockContents = True
                      End If
                    End With
                  Next
                End With
                If Format(Str1, "YYYYMMDD") > Format(Str2, "YYYYMMDD") Then
                  MsgBox "'Visit Date - From' is greater than 'Visit Date - To'" & _
                  vbCr & vbTab & "Please re-input the correct dates", vbCritical
                ElseIf Format(Str1, "YYYYMMDD") = Format(Str2, "YYYYMMDD") Then
                  MsgBox "'Visit Date - From' is equal to 'Visit Date - To'" & _
                  vbCr & vbTab & "Please re-input the correct dates", vbCritical
                End If
              Else
                If Format(Str1, "YYYYMM") = Format(Str2, "YYYYMM") Then
                  Str1 = Format(Str1, "D") & " to "
                ElseIf Format(Str1, "YYYY") = Format(Str2, "YYYY") Then
                  Str1 = Format(Str1, "D MMMM") & " to "
                End If
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        .Range.Text = Str1
                      Else
                        .LockContents = False
                        .Range.Text = Str1
                        .LockContents = True
                      End If
                    End With
                  Next
                End With
                With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
                  For i = 1 To .Count
                    With .Item(i)
                      If .Type = wdContentControlDate Then
                        .Range.Text = Str2
                      Else
                        .LockContents = False
                        .Range.Text = Str2
                        .LockContents = True
                      End If
                    End With
                  Next
                End With
              End If
        End With
        Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 03-25-2014 at 04:04 PM. Reason: Deleted redundant, commented-out code line
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #49
    Nice one macropod, it's working nicely now. I just placed the code in a copy of the template on my laptop and it works. I will test it through sharepoint tomorrow morning.

    Out of interest, do I need to ensure that my date picker controls are formatted in line with the code:

    If Format(Str1, "YYYYMMDD") >= Format(Str2, "YYYYMMDD") Then
    or is that the way the code works. The format of our dates is DD MMMM YYYY.

    Thanks for sticking with it, I can't thank you enough.

    Regards,

    JDS_916

  10. #50
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    That format is only used for a string comparison. It has no bearing on the content control formats - which the code messes around with anyway.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #51
    Should I use date picker controls on the introduction page or keep the text content controls. It seems to work with either type of control on the introduction page. I assume that the code looks for controls by title.

  12. #52
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I'd put the date pickers on the intro page (or whatever page you want the users to be able to input the data on) - the others get locked to prevent input.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #53
    Okay cheers. The users fill the fields out on the document information panel (set up in MS Infopath). These fields are mapped onto the template (title page and introduction page) using Insert > Quick Parts > Document Property. The dates get entered onto the title page and introduction page. The actual update of the fields on the introduction page happens through the Filesave macro. Thanks to your work, all this is achieved without the user having to muck around on the actual document etc.

    Regards,

    JDS_916

  14. #54
    Strange, it works when the months are different (e.g. 3 March - 5 April 2014), but not when the 'From' and 'To' months selected are the same. In saying that, the 'Visit Date - From' field on the document information panel and the 'Visit Date - From' content controls in the document change to display the day only, but the field and content control display a red border and do not let me save the file. I've checked everything and there are no strange settings anywhere.

    Regards,

    JDS_916

  15. #55
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I suspect your issue has more to do with the fields than the content controls, in that they probably accept D MMMM or D MM as a date but not D on its own.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  16. #56
    I think you're right. The 'Visit Date - From' control on the title page says 'Only date or date and time allowed', but that's only because the field on the DIP is in error. The macro before seem to ignore the DIP field and worked on the controls only.

    Thinking about it, even if I could change the format, you would not really want the 'dd' format to show in the 'Visit Date - From' column in the sharepoint library because it would look a bit strange, especially as users can filter documents by date.

    I can't believe that it's so close. It seems to be a mixture of the previous code and yesterday's code. The date selected in the field on the DIP, which essentially is a date column in the sharepoint library needs to remain in dd/mm/yyyy or whatever is set locally.


    Regards,

    JDS_916
    Attached Images Attached Images
    Last edited by JDS_916; 03-26-2014 at 08:52 AM.

  17. #57
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Unless whatever your DIP field is uses a content control named 'Visit Date - From', there's nothing in the macro that could affect it. The only possibility I can think of is that your DIP field is reading its data back from the content control's text instead of its value.

    PS: your attached thumbnail is almost indecipherable and I can't see your DIP field.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  18. #58
    Sorry about the image, it was just a few columns from the sharepoint library. I'm trying to get the StyleRef method to work, but this solution means that the user could delete the ref fields easily on the introduction page.

    Regards,

    JDS_916

  19. #59
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Why would there be REF fields? STYLEREF fields work with Styles, not REF fields. In any event, you could put the fields inside a non-editable text content control, using code like:
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim Str1 As String, Str2 As String, i As Long
    With ContentControl
      If (.Title = "Visit Date - From") Or (.Title = "Visit Date - To") Then
        With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
          For i = 1 To .Count
            With .Item(i)
              If .Type = wdContentControlDate Then
                If .Range.Text = .PlaceholderText Then Exit Sub
                .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
                Str1 = .Range.Text
                Exit For
              End If
            End With
          Next
        End With
        With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
          For i = 1 To .Count
            With .Item(i)
              If .Type = wdContentControlDate Then
                If .Range.Text = .PlaceholderText Then Exit Sub
                .Range.ParentContentControl.DateDisplayFormat = "D MMMM YYYY"
                Str2 = .Range.Text
                Exit For
              End If
            End With
          Next
        End With
        If Format(Str1, "YYYYMMDD") >= Format(Str2, "YYYYMMDD") Then
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
            For i = 1 To .Count
              With .Item(i)
                If .Type = wdContentControlDate Then
                  .Range.Text = ""
                Else
                  .LockContents = False
                  .Range.Text = ""
                  .LockContents = True
                End If
              End With
            Next
          End With
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
            For i = 1 To .Count
              With .Item(i)
                If .Type = wdContentControlDate Then
                  .Range.Text = ""
                Else
                  .LockContents = False
                  .Range.Text = ""
                  .LockContents = True
                End If
              End With
            Next
          End With
          If Format(Str1, "YYYYMMDD") > Format(Str2, "YYYYMMDD") Then
            MsgBox "'Visit Date - From' is greater than 'Visit Date - To'" & _
            vbCr & vbTab & "Please re-input the correct dates", vbCritical
          ElseIf Format(Str1, "YYYYMMDD") = Format(Str2, "YYYYMMDD") Then
            MsgBox "'Visit Date - From' is equal to 'Visit Date - To'" & _
            vbCr & vbTab & "Please re-input the correct dates", vbCritical
            'ActiveDocument.SelectContentControlsByTitle("Visit Date - From")(1).Range.Text = "on "
          End If
        Else
          If Format(Str1, "YYYYMM") = Format(Str2, "YYYYMM") Then
            Str1 = Format(Str1, "D") & " to "
          ElseIf Format(Str1, "YYYY") = Format(Str2, "YYYY") Then
            Str1 = Format(Str1, "D MMMM") & " to "
          End If
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - From")
            For i = 1 To .Count
              With .Item(i)
                If .Type = wdContentControlDate Then
                  .Range.Text = Str1
                Else
                  .LockContents = False
                  .Range.Text = Str1
                  .LockContents = True
                End If
              End With
            Next
          End With
          With ActiveDocument.SelectContentControlsByTitle("Visit Date - To")
            For i = 1 To .Count
              With .Item(i)
                If .Type = wdContentControlDate Then
                  .Range.Text = Str2
                Else
                  .LockContents = False
                  .Range.Text = Str2
                  .LockContents = True
                End If
              End With
            Next
          End With
        End If
        With ActiveDocument.SelectContentControlsByTitle("Visit Date - Range")
          For i = 1 To .Count
            With .Item(i)
              .LockContents = False
              .Range.Fields(1).Update
              .LockContents = True
            End With
          Next
        End With
      End If
    End With
    Application.ScreenUpdating = True
    End Sub
    In the above, the output content controls are titled 'Visit Date - Range'.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #60
    Sorry I meant StyleRef fields. The title page is formatted as follows:

    Visit Dates: ss [Insert Date] ss - ss [Insert Date]

    The above text and date controls are in a table cell. I created to new styles for the date controls and used style separators (ss). I then placed StyleRef fields on the introduction page. This all works, but the style separators, although appear to be doing their job by displaying everything on one line on my screen, do not work for some reason. When I go to print the page, the text appears as follows:

    Visit Dates: ss
    [Insert Date] ss
    - ss
    [Insert Date]

    Many thanks for the revised code, I'll try it first thing in the morning.

    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
  •