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.
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:
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: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
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.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?
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.
Regards,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
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
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.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
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,
Where do I look to see what's in Str1 and Str2?Quote:
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?Code: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.
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