Date Picker Control

[Patrickw]

I want to use the date picker control on a userform in Word. When I
close the form with the OK button, I want to search the term
(<<Court_Date>>) or multiple instances of the term in a document with
the formatted value from the date picker control. I am not using
bookmarks because I am running a mailmerge before opening the
userform, which strips the bookmarks from the document.

Put another way, how do I assign the formatted date value from the
date picker control to a string variable?

 

[Doug Robbins - Word MVP]

I would use DOCVARIABLE fields in the document and then have code in the
userform set the .Value of the variable to the formatted date selected from
the date picker control and then update the fields in the document so that
the date was displayed

With ActiveDocument
.Variables("varCourt_Date").Value = Format(calCourtDate.Value, "d MMMM
yyyy")
.Range.Fields.Update
End With



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Patrickw]

Thanks. I'll give it a try. I like the idea of using DOCVARIABLE
fields instead of searching and replacing text. It seems to be a much
more elegant solution.

 

[Patrickw]

Tried some code as follows:

1. I added the following on the document page: Your court date is
{ DOCVARIABLE varCourtDate \@ "dddd, MMMM dd, yyyy" \*
MERGEFORMAT }

2. I added the Microsoft Date and Time Picker Control, Version 6.0
(DTControl) to a userform (USRCourtDate) along with an OK command
button.

3. To the DTControl (CALCourtDate), I added the following code:

Private Sub CALCourtDate_AfterUpdate()
With ActiveDocument
.Variables("varCourtDate").Value = Format
(CALCourtDate.Value, "dddd, mmmm d, yyyy")
End With
End Sub

4. To the OK command button (CMDok, I added the following code:

Private Sub CMDok_Click()
With ActiveDocument
.Range.Fields.Update
End With
USRCourtDate.hide
Unload USRCourtDate
End Sub

Still, the document field doesn't update when I run the macro, update
the DTControl with a new date and press OK.

 

[Patrickw]

Actually, as much as I would like to, I don't think I'll be able to
use fields, because, like bookmarks, fields are stripped from the
document following the mail merge. I am using a third party client
managment application called Time Matters. After it merges a
document, but before it will execute any macros, the program saves the
semi-finished document to a client folder and unloads the template
from which the document was created, thus stripping bookmarks and
fields. It relies on all macros being stored in the normal template
to execute any post-merge macros with respect to the document. That
is why I have to use the cludgy search and replace technique. To
distinguish my find terms, I surround some variable text, say
"Court_Date" with chevrons, like <<Court_Date>> within the document.
Then I search the document for the term <<Court_Date>> and replace it
with the text returned from the userform. I can get text from all the
standard controls, like text boxes, as strings, but I cannot seem to
retrieve the data from the DTControl and convert it to a string that I
can use in the replace function.

 

[Graham Mayor]

All you really need in the way of code is

Private Sub CMDok_Click()
With ActiveDocument
.Variables("varCourtDate").Value = _
Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy")
.Range.Fields.Update
End With
Unload Me
End Sub

and the field in the document should be simply

{ DocVariable "varCourtDate"}

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

OK plan B - using this method the code for the OK button would be

Private Sub CMDok_Click()
Dim sDate As String
sDate = Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy")
ActiveDocument.Range.Text = Replace(ActiveDocument.Range.Text, _
"<<Court_Date>>", sDate)
Unload Me
End Sub

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Patrickw]

Thanks. Works perfectly. I like the ActiveDocument.Range.Text object
so much I am going back and replacing all my With Selection.Find
functions. Last question. How do I get the replacement text to match
the format of the replaced text (italics, for instance)?

 

[Graham Mayor]

Unfortunately you trade simplicity for a rather blunt stick using this
method. If you want to maintain the formatting you need to narrow the range
to the text string you want to replace e.g.

Private Sub CMDok_Click()
Dim sDate As String
Dim oRng As Range
sDate = Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy")
With Selection
.HomeKey wdStory
With .Find
Do While .Execute(findText:="<<Court_Date>>", _
MatchWildcards:=False)
Set oRng = Selection.Range
oRng.Text = sDate
Loop
End With
End With
Unload Me
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Patrickw]

Thanks Graham and Doug. You guys are awesome. For others following
this post, my final code is as follows:

Dim oRng As Range
Dim strFindText As String
Dim strHearingType As String
Dim strCourtDate As String

Private Sub CMDok_Click()

' Replace <<Hearing_Type>> in body of document
strFindText = "<<Hearing_Type>>"
strHearingType = TXTHearingType.Value
Call RangeReplace(strFindText, strHearingType)

' Replace <<HEARING_TYPE>> in body of document
strFindText = "<<HEARING_TYPE>>"
strHearingType = UCase(TXTHearingType.Value)
Call RangeReplace(strFindText, strHearingType)

' Replace <<Court_Date>> in body of document
strFindText = "<<Court_Date>>"
strCourtDate = Format(CALCourtDate.Value, "dddd, mmmm d, yyyy")
Call RangeReplace(strFindText, strCourtDate)

' Replace <<COURT_DATE>> in body of document
strFindText = "<<COURT_DATE>>"
strCourtDate = UCase(Format(CALCourtDate.Value, "dddd, mmmm d,
yyyy"))
Call RangeReplace(strFindText, strCourtDate)

' Hide and Unload the userform
USRHearingInfo.hide
Unload USRHearingInfo

End Sub
Function RangeReplace(strTextToFind, strTextToSubstitute)

With Selection
.HomeKey wdStory
With .Find
Do While .Execute(findText:=strFindText, MatchCase:=True,
MatchWildcards:=False)
Set oRng = Selection.Range
oRng.Text = strTextToSubstitute
Loop
End With
End With

End Function


It is compact and easily modified into more complex forms.

 

[Patrickw]

I made one last tweek for the sake of consistency and code
readability.

In the function, I changed findText:=strFindText to
findText:=strTextToFind. Now, the first line of the Do While loop
reads as follows:

Do While .Execute(findText:=strTextToFind,
MatchCase:=True, _
MatchWildcards:=False)


This makes it clearer that I'm utilizing the more general argument
being passed to the function.

 

[Graham Mayor]

You could make it clearer still

Private strTextToFind(3) As Variant
Private strTextToSubstitute(3) As Variant

Private Sub CMDok_Click()
strTextToFind(0) = "<<Hearing_Type>>"
strTextToFind(1) = "<<HEARING_TYPE>>"
strTextToFind(2) = "<<Court_Date>>"
strTextToFind(3) = "<<COURT_DATE>>"

strTextToSubstitute(0) = TXTHearingType.Value
strTextToSubstitute(1) = UCase(TXTHearingType.Value)
strTextToSubstitute(2) = Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy")
strTextToSubstitute(3) = UCase(Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy "))

For i = LBound(strTextToFind) To UBound(strTextToFind)
With ActiveDocument.Range.Find
.Execute findText:=strTextToFind(i), _
MatchCase:=True, _
MatchWildcards:=False, _
Replacewith:=strTextToSubstitute(i), _
Replace:=wdReplaceAll
End With

Next i

Unload Me
End Sub


or retaining the function

Private oRng As Range
Private strTextToFind(3) As Variant
Private strTextToSubstitute(3) As Variant

Private Sub CMDok_Click()
strTextToFind(0) = "<<Hearing_Type>>"
strTextToFind(1) = "<<HEARING_TYPE>>"
strTextToFind(2) = "<<Court_Date>>"
strTextToFind(3) = "<<COURT_DATE>>"

strTextToSubstitute(0) = TXTHearingType.Value
strTextToSubstitute(1) = UCase(TXTHearingType.Value)
strTextToSubstitute(2) = Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy")
strTextToSubstitute(3) = UCase(Format(CALCourtDate.Value, _
"dddd, mmmm d, yyyy "))

For i = LBound(strTextToFind) To UBound(strTextToFind)
Call RangeReplace(strTextToFind(i), strTextToSubstitute(i))
Next i
Unload Me
End Sub

Function RangeReplace(strTextToFind, strTextToSubstitute)
With ActiveDocument.Range.Find
.Execute findText:=strTextToFind, _
MatchCase:=True, _
MatchWildcards:=False, _
Replacewith:=strTextToSubstitute, _
Replace:=wdReplaceAll
End With
End Function

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>