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

[JDS_916]

Hi macropod,

I placed the line of code in my FileSave() macro and it works but only after the file has been saved first. When I click on save a second time, the fields change! I'm not sure if this is the best code for saving a file for the first time because it certainly does not work if the user decides to click on Save As instead of Save.

Sub FileSave()
  Dim strfName
      With ActiveDocument
          If Len(.Path) = 0 Then
              strfName = .ContentTypeProperties("RO").Value & Chr(32) & _
              .CustomDocumentProperties("OfficeType").Value & Chr(32) & _
              .ContentTypeProperties("Country(s)").Value & Chr(32) & _
              .CustomDocumentProperties("ReportType").Value
              .BuiltInDocumentProperties("Title").Value = strfName
           With Dialogs(wdDialogFileSaveAs)
                  .Name = strfName & ".docx"
                  .Show
              End With
          Else
              Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1), False)
              .Save
          End If
      End With
  End Sub

Regards,

JDS_916

P.S I think the Save and Save As are both working now.

[macropod]

Try making the Call the first line, or thereabouts, in your Sub.

[JDS_916]

Hi macropod,

It works, thanks a million. I placed the line near the beginning as you said...

Sub FileSave()
  Dim strfName
    Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1), False)
      With ActiveDocument
          If Len(.Path) = 0 Then
              strfName = .ContentTypeProperties("RO").Value & Chr(32) & _
              .CustomDocumentProperties("OfficeType").Value & Chr(32) & _
              .ContentTypeProperties("Country(s)").Value & Chr(32) & _
              .ContentControls(1).Range.Text & Chr(32) & _
              .CustomDocumentProperties("ReportType").Value
              .BuiltInDocumentProperties("Title").Value = strfName
          With Dialogs(wdDialogFileSaveAs)
                  .Name = strfName & ".docx"
                  .Show
              End With
          Else
             .Save
          End If
      End With
  End Sub

May I bother you with two further issues that I came across:

1. If the user has made a mistake in filling out the fields on the document information panel and the file has already been saved once, the macro no longer updates the Title field with any new values (I guess because of...

If Len(.Path) = 0 Then

Is it possible for the macro to always perform this check and update (if needed) before resuming with regular saves.

2. Is it possible for the user to receive a message when two dates match or the From date is greater than the To date etc. At the moment the field in error shows 'on' for matching dates and "" for from > to and to < from. My may concern is that it's possible to Save the file with incorrect dates. The columns in the sharepoint library show blanks where the dates have been entered in error.

Kind regards,

JDS_916

[macropod]

For the first issue, try:

Sub FileSave()
    Dim strfName
    Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1), False)
    With ActiveDocument
        strfName = .ContentTypeProperties("RO").Value & Chr(32) & _
        .CustomDocumentProperties("OfficeType").Value & Chr(32) & _
        .ContentTypeProperties("Country(s)").Value & Chr(32) & _
        .ContentControls(1).Range.Text & Chr(32) & _
        .CustomDocumentProperties("ReportType").Value
        If .BuiltInDocumentProperties("Title").Value <> strfName Then
            .BuiltInDocumentProperties("Title").Value = strfName
            With Dialogs(wdDialogFileSaveAs)
                .Name = strfName & ".docx"
                .Show
            End With
        Else
            .Save
        End If
    End With
End Sub

For the second issue, try:

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
            If 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 greater than 'Visit Date - To'" & _
                    vbCr & vbTab & "Please re-input the correct dates", vbCritical
            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' to '"
                        Else
                            .Range.ParentContentControl.DateDisplayFormat = "D MMMM' to '"
                        End If
                    End If
                End With
            End If
        End If
    End With
    Application.ScreenUpdating = True
End Sub

[JDS_916]

Thanks a lot macropod. I'll be trying this first thing tomorrow morning.

Kind regards,

JDS_916

[JDS_916]

Hi macropod,

The revised FileSave() macro amends the title field based on any changes made to other fields on the document information panel (after the document has been saved once). However, it goes on to display the SaveAs dialog when all I want it to do is save the changes. The SaveAs is still required for creating the actual document.

The message boxes display the errors when dates are entered incorrectly. However, when I click on okay in the message box, the FileSave() macro continues to run. I suppose it's tricky because this code runs as part of the FileSave() macro. I really need the user to enter the correct dates before the document is saved. Is it possible to exit or cancel out of the macro and keep forcing the user to enter a valid date range.

Regards,

JDS_916

[snb]

A return to your first question:

[macropod]

The revised FileSave() macro amends the title field based on any changes made to other fields on the document information panel (after the document has been saved once). However, it goes on to display the SaveAs dialog when all I want it to do is save the changes. The SaveAs is still required for creating the actual document.

As coded, the Save As is invoked if there's been a change to the components that constitute "Title". This, of course, applies when the file is created, but also when any of the input details are changed. It goes straight through to the save, though, if those details haven't changed. Isn't that what you wanted?

The message boxes display the errors when dates are entered incorrectly. However, when I click on okay in the message box, the FileSave() macro continues to run. I suppose it's tricky because this code runs as part of the FileSave() macro. I really need the user to enter the correct dates before the document is saved. Is it possible to exit or cancel out of the macro and keep forcing the user to enter a valid date range.

To handle that, you could change:

Sub FileSave() 
    Dim strfName 
    Call Document_ContentControlOnExit(ActiveDocument.SelectContentControlsByTitle("Visit Date - To")(1), False) 
    With ActiveDocument

to:

Sub FileSave()
    Dim strfName
    With ActiveDocument
        Call Document_ContentControlOnExit(.SelectContentControlsByTitle("Visit Date - To")(1), False)
        With .SelectContentControlsByTitle("Visit Date - To")(1)
          If .Range.Text = .PlaceholderText Then Exit Sub
        End With
        With .SelectContentControlsByTitle("Visit Date - From")(1)
          If .Range.Text = .PlaceholderText Then Exit Sub
        End With

[JDS_916]

Hi macropod,

Everything is perfect now, except that if you hit SaveAs instead of Save when first saving the document, the macros are not triggered. Can the FileSave() macro be changed to work on Save or SaveAs.

Regards,

JDS_916

[macropod]

Simple:

Sub FileSaveAs()
Call FileSave
End Sub

[JDS_916]

Hi macropod,

Many thanks. I did discover one thing. The 'Visit Date - From' and 'Visit Date - To' content controls are in two places in the document, the title page and introduction. I noticed today that the second set of controls are not being changed. Is it possible to run the macro over the second set of controls as well.

Regards,

JDS_916

[macropod]

Are you saying there are two places in the document with pairs of date-picker content controls into which quite different dates can be input? Or is the content of the second set tied to whatever is input in the first set? If it's the latter, see the content controls section at: http://gregmaxey.mvps.org/word_tip_p...ting_data.html

[JDS_916]

The latter. Dates are entered via the document information panel into the first set of controls (title/cover page) and then to the second set on the introduction page. There are other controls in the document, i.e. 'RO', 'Country', etc. which behave exactly as described in section provided. If i set the dates directly on the title page (not through the dip), for example in the same month, and keep the cursor in the field preventing on exit, I can scroll to the introduction page and see the same dates, which is how it should work. I then go back and trigger on exit and the macro works perfectly by removing the month in the 'Visit Date - From', but this does not show in the second 'Visit Date - From' control on the introduction page.

Regards,

JDS_916

[macropod]

The link in my last post shows how you can handle that.

[JDS_916]

Ah yes, 'simple linked content controls'; just tried it and it worked. Macropod you're a scholar and a gentleman!

Finally, I thought I'd be smart and add this to the FileSave macro...

With .SelectContentControlsByTitle("Recognised Organisation")(1) 
           If .Range.Text = .PlaceholderText Then Exit Sub
        End With

It works, but I need it somehow to display a msgbox to say that the field cannot be blank before the Exit Sub.

Regards,

JDS_916

[macropod]

That's pretty basic -

With .SelectContentControlsByTitle("Recognised Organisation")(1)
  If .Range.Text = .PlaceholderText Then
    MsgBox "Dumbkopf! You need a Recognised Organisation", vbExclamation
    Exit Sub
  End If
End With

(or words to that effect)...

[JDS_916]

Hi macropod

I thought I'd cracked it the other evening after setting the template up using simple linked content controls. Applying from and to dates on the title page and triggering the macro was showing the correct results on the introduction page. However, when I updated the template in Sharepoint and tested it, I get the message:

The document contains links that may refer to other files. Do you want to update this document with data from linked files.

Clicking yes or no crashes Word. It attempts to recover information and then stops working. You are then prompted to close the program. Next time round you are asked whether you want to start Word in safe mode.

Regards,

JDS_916

[macropod]

I guess that has something to do with your document other than the content controls, since they have nothing to do with linking to external files. It's also possible your document has some form of corruption. Corrupt documents can often be 'repaired' by inserting a new, empty, paragraph at the very end, copying everything except that new paragraph to a new document based on the same template, closing the old document and saving the new one over it. Similarly, corrupt tables can often be 'repaired' by converting the tables to text and back again or by saving the document in RTF format, closing the document then re-opening it and re-saving in the doc(x) format.

[JDS_916]

As soon as you add ole_links to a document it displays the message, even if a link is from one paragraph to the next in the same document. If I get it working, is it possible to stop the message from displaying each time the document is opened. I've read a number of posts regarding this issue and it appears to be pretty annoying.

[macropod]

But nothing in the code I've posted has anything to do with 'ole_links'; it neither creates nor references them. Evidently there's something else going on.