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

[JDS_916]

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.

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[macropod]

Cross-posted at: http://answers.microsoft.com/en-us/office/forum/office_2010-customize/how-to-change-date-picker-format-based-on-date/f28ebd1c-71ef-425b-98bb-e1aace36b93e
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[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_pages/repeating_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.

[JDS_916]

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.

[macropod]

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.

[JDS_916]

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!

[macropod]

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

[JDS_916]

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

[macropod]

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

[JDS_916]

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

[macropod]

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

[JDS_916]

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

[macropod]

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.

[JDS_916]

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.

[macropod]

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.

[JDS_916]

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

[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

[macropod]

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.

[JDS_916]

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

[macropod]

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.

[JDS_916]

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

[macropod]

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

[JDS_916]

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

[macropod]

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.

[JDS_916]

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.

[macropod]

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.

[JDS_916]

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

[JDS_916]

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.

[macropod]

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.

[JDS_916]

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

[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:


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:


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

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


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

[macropod]

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/InterceptSavePrint.htm

[JDS_916]

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:


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


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



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

[macropod]

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.

[JDS_916]

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

[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

[macropod]

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.

[JDS_916]

Hello macropod,


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:


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

[macropod]

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.

[JDS_916]

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

Regards,

JDS_916

[macropod]

In that case, try the alternative approach.

[JDS_916]

Hi macropod,

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

Regards,

JDS_916

[macropod]

Msgbox "Str1: " & Str1 & vbCr & "Str2: " & Str2