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

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.

Quote:

---

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.

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.

Code:

---

                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.

I guess that it's in...

Code:

---

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

---

Code:

---

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!

No, here:

Code:

---

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

---

I receive an error when I place the code where stated:

Code:

---

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

Sorry, when I copy/pasted the code for post #42, I missed the closing 'End With' statement

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

Try this code:

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

---

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:

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

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.

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.

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.

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

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

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.

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

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.

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

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:

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
    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'.

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

If you apply one character Style to both content controls, with nothing (including a Style separator) between them, a single StyleRef field will pick up both.

Again it works, but the dip field (Visit Date - From) does not allow just 'dd' format. I discovered that you can also bookmark the controls on the title page and cross reference them on the introduction page, which is slightly cleaner in this case than using the StyleRef field and style separators. There was one other issue that came up regarding text controls in that the macro displayed a message stating that it could only do a particular action on date controls.

I have no idea what your DIP field is, but it seems to me you're populating it from the content control's text instead of from its underlying date value. You need to change that process so the DIP field reads the content control's underlying date value.

The field on the DIP is just a sharepoint column created in the library. There is no process I can change.

You set up columns for your library. There are a number of different column types available. These columns are then mapped automatically to the DIP as fields.

Well, your DIP field is getting its data from somewhere; in this case, from the "Visit Date - From" content control. The problem is, though, that its text ceases to be a valid date as soon as the code starts playing around with it. As I see it, you have a few options:
1. Leave the "Visit Date - From" content control on the cover page alone and do all the format manipulations only on the related controls elsewhere in the document;
2. Change the DIP field type to text;
3. Use a different content control to output the "Visit Date - From" value in its native date format and link the DIP field to that.

I'll try your suggestions tomorrow morning. There are calculated columns and values that would do the job without any problems in sharepoint, however, you cannot get them to appear on the DIP. The view is that calculated columns and values are non-editable, so why would you want them on the DIP. I would have to start looking into workflows, event receivers, etc.

Regards,

JDS_916

It works with the following configuration. So, date picker content controls on the title page mapped from the DIP (insert > quick parts > document property) each with its individual style (FromDate and ToDate). These styles are cross referenced (StyleRef) on the introduction page. This works with the following code:

Code:

---

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)    Application.ScreenUpdating = False
    Dim i As Long
    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"
                            '.Range.ParentContentControl.DateDisplayFormat = "D' - '"
                        Else
                            .Range.ParentContentControl.DateDisplayFormat = "D MMMM"
                            '.Range.ParentContentControl.DateDisplayFormat = "D MMMM' - '"
                        End If
                    End If
                End With
            End If
        End If
    End With
    Application.ScreenUpdating = True
End Sub

---

However, I could not get the field codes (StyleRef, Bookmark Ref, etc.) to work when embedded in text or rich text controls. The template crashes with the following message:

Quote:

---

Word found unreadable content in “Technical Report Template.dotm”. Do you want to recover the contents of this document? If you trust the source of this document, click yes?

---

The main drawback with this setup is that the cross references on the introduction page can be deleted by the user easily and are not updated automatically (only by F9 or ensuring update fields before printing is checked). Perhaps the update can be achieved by the FileSave() macro.

Once again, thanks macropod for your code and time.

Regards,

JDS_916

Good afternoon to all,

The FileSave() macro that I'm using can be bypassed if a user decides to close the window and then chooses to save the file. The macro is not initiated and the user can enter any filename. I would like to avoid all possible scenarios whereby documents are saved to my sharepoint library with missing metadata. Any help would be greatly appreciated.

Code:

---

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
        With .SelectContentControlsByTitle("Recognised Organisation")(1)
            If .Range.Text = .PlaceholderText Then
              MsgBox "Please select a Recognised Organisation", vbExclamation
              Exit Sub
        End If
        End With
            strfName = .ContentTypeProperties("RO").Value & Chr(32) & _
              .CustomDocumentProperties("OfficeType").Value & Chr(32) & _
              .ContentTypeProperties("Location(s)").Value & Chr(32) & _
              .ContentControls(7).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

---

Regards,

JDS_916

Probably what you need is to enable Word's DocumentBeforeSave event_handler and use that. The process is outlined at:
http://word.mvps.org/FAQs/MacrosVBA/AppClassEvents.htm
http://word.mvps.org/FAQs/MacrosVBA/...tSavePrint.htm

Good morning all,

I'm in a bit of a sticky situation at the moment with my users. Some of them are receiving a message 'subscript out of range' when saving changes to a document in sharepoint. Everything was working fine and now this message seems to crop up when it feels like it. Why does the document save a few times and then decide not to, it's odd. Furthermore, the message does not show the debug button so I can't see the area of code. I'm desperate to resolve this problem as the system is live so any help would be really appreciated.

The code is as follows:

Code:

---

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
        With .SelectContentControlsByTitle("Recognised Organisation")(1)
            If .Range.Text = .PlaceholderText Then
              MsgBox "Please select a Recognised Organisation", vbExclamation
              Exit Sub
        End If
        End With
            strfName = .ContentTypeProperties("RO").Value & Chr(32) & _
              .CustomDocumentProperties("OfficeType").Value & Chr(32) & _
              .ContentTypeProperties("Location(s)").Value & Chr(32) & _
              .ContentControls(7).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

---

and

Code:

---

Sub FileSaveAs()
    'On Error GoTo Oops
   
    If Len(ActiveDocument.Path) = 0 Then
        Call FileSave
    ElseIf Len(ActiveDocument.Path) >= 0 Then
        Dialogs(wdDialogFileSaveAs).Show
    End If
     
'Oops:
End Sub

---

Just realized that when the user clicks 'Save' and the message decides to appear, by clicking OK, luckily the Save is still made, which is at least something.

Ah! I've just managed to get the message with the debug and it points to: If Split(Str1, " ")(2) = Split(Str2, " ")(2) Then

Code:

---

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)


________HERE                  If Split(Str1, " ")(2) = Split(Str2, " ")(2) Then              HERE___________
                       

                            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
            End If
        End If
    End With
    ActiveDocument.PrintPreview
    ActiveDocument.ClosePrintPreview
    Application.ScreenUpdating = True
End Sub

---

Regards,

JDS_916

Without knowing what triggers an intermittent error, resolving it is liable to be hit-and-miss. However, given the interactions between your Content Controls and Document Properties, I wouldn't be surprised if, for example, the user has input something into a Content Control but hasn't exited it. In that case, your exit event won't fire and consequential updates won't occur. That could lead to the 'subscript out of range' errors.

Hello macropod,

The users at this stage are just writing the report in Word. The content controls in the document have already been filled through entries made by the user via the DIP and the file saved with the unique name. The users do not go near the content controls in the document nor do they change anything in the DIP or document properties, so it's odd how this can occur.

Regards,

JDS_916

Good afternoon,

Can someone please help me with my runtime error 09 'subscript out of range'? I have no idea where to start looking nor what to do! The code does the job and even allows Saves, but every now and again this annoying message appears. As described in my earlier post, users are just typing and saving (nowhere near any of the content controls).

I read somewhere that this error can sometimes be linked to the windows registry so I ran 'CCleaner' on a number of workstations but the message reappeared after a while.

Kind regards,

JDS_916

Until you can isolate the particular circumstances under which the error occurs, no one can help fix it. The fact the error message is occurring on the line with 'If Split(Str1, " ")(2) = Split(Str2, " ")(2) Then' suggests either or both of the referenced content controls don't have the second array element the Split function is looking for. You need to tell us what's in Str1 & Str2 when the error occurs.

Hello macropod,

Quote:

---

You need to tell us what's in Str1 & Str2 when the error occurs.

---

Where do I look to see what's in Str1 and Str2?

I click 'Save', the message appears, I click debug and it takes me to:

Code:

---

Split(Str1, " ")(2) = Split(Str2, " ")(2)

---

and that's it. Where would I go from here?

Normally the user has finished a sentence or paragraph in the document and the cursor is blinking at the paragraph mark, he clicks Save and the message appears.

Regards,

JDS_916

Assuming that line is highlighted, hovering the mouse over each of the Str1, Str2 items in the code should tell you what they contain. Alternatively, you could insert a message box to output Str1 and Str2 immediately before the offending line of code.

The 'Debug' button does not appear on the message (only OK and Help). There is no password on the project.

Regards,

JDS_916

In that case, try the alternative approach.

Hi macropod,

Could you kindly assist with the msgbox code as I have no idea how to go about doing this.

Regards,

JDS_916