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.
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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.
Last edited by JDS_916; 03-27-2014 at 04:48 AM.
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
The field on the DIP is just a sharepoint column created in the library. There is no process I can change.
Last edited by JDS_916; 03-27-2014 at 09:46 AM.
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.
Last edited by JDS_916; 03-27-2014 at 10:04 AM.
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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:
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: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
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.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?
Once again, thanks macropod for your code and time.
Regards,
JDS_916
Last edited by JDS_916; 03-30-2014 at 05:30 AM.
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.
Regards,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
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
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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
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.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
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
Last edited by JDS_916; 05-14-2014 at 07:41 AM.
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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
Last edited by JDS_916; 05-15-2014 at 03:09 AM.
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
Last edited by JDS_916; 05-21-2014 at 08:42 AM.
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Hello macropod,
Where do I look to see what's in Str1 and Str2?You need to tell us what's in Str1 & Str2 when the error occurs.
I click 'Save', the message appears, I click debug and it takes me to:
and that's it. Where would I go from here?Split(Str1, " ")(2) = Split(Str2, " ")(2)
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
The 'Debug' button does not appear on the message (only OK and Help). There is no password on the project.
Regards,
JDS_916
Last edited by JDS_916; 05-22-2014 at 09:01 AM.
In that case, try the alternative approach.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Hi macropod,
Could you kindly assist with the msgbox code as I have no idea how to go about doing this.
Regards,
JDS_916