Consulting

Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 61 to 80 of 81

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

  1. #61
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  2. #62
    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.

  3. #63
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  4. #64

    Red face

    The field on the DIP is just a sharepoint column created in the library. There is no process I can change.
    Attached Files Attached Files
    Last edited by JDS_916; 03-27-2014 at 09:46 AM.

  5. #65
    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.
    Attached Files Attached Files
    Last edited by JDS_916; 03-27-2014 at 10:04 AM.

  6. #66
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  7. #67
    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

  8. #68
    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
    Last edited by JDS_916; 03-30-2014 at 05:30 AM.

  9. #69
    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

  10. #70
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  11. #71
    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
    Last edited by JDS_916; 05-14-2014 at 07:41 AM.

  12. #72
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  13. #73
    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.

  14. #74
    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.

  15. #75
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  16. #76
    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

  17. #77
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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]

  18. #78
    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.

  19. #79
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    In that case, try the alternative approach.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #80
    Hi macropod,

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

    Regards,

    JDS_916

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •