Hi Paul,
I have a followup. Though the code works and inserts the value into the cell, is there a way to insert that into a field or content control. The reason being is I used the age to programatically insert text based on age range.
For reference, when i used form fields, this was the code I used for calculating age and inserting text based on age to a bookmark.
Code:
Public Sub CalcAge()
'This code will only work if you have two form fields named
'BirthDate and Age in the active document. In the properties of
'the DateOfBirth field, set the "Run Macro on Exit" value to the name
'of this procedure.
Dim objAge As Word.FormField
Dim objBirthDate As Word.FormField
Dim datToday As Date
Dim intAge As Integer
Dim intYears As Integer
'Set today's date
datToday = Date
'Get the form fields associated with the birthdate and the age.
Set objAge = ActiveDocument.FormFields("Age")
Set objBirthDate = ActiveDocument.FormFields("DateOfBirth")
'If a valid date is entered in the BirthDate field, calculate the age.
If IsDate(objBirthDate.Result) = False Then
objAge.Result = ""
GoTo Line2
Else
GoTo Line1
End If
Line1:
' Find difference in calendar years.
intYears = DateDiff("yyyy", objBirthDate.Result, datToday)
If intYears > 0 Then
' Decrease by 1 if current date is earlier than birthday of current year
' using DateDiff to ignore a time portion of datDateOfBirth.
intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, objBirthDate.Result)) > 0)
End If
objAge.Result = intAge
'GoTo Line5
Line2:
Set objAge = Nothing
Set objBirthDate = Nothing
' check for bookmark - if it isn't there, form will close
If ActiveDocument.Bookmarks.Exists("testing") = True Then
' if bookmark exisits, it will enter the number from the form into the bookmark
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect
Else
End If
Application.ScreenUpdating = False
Dim BMRange As Range
'Identify current Bookmark range and insert text
Set BMRange = ActiveDocument.Bookmarks("testing").Range
'If IsNull(ActiveDocument.Bookmarks("Age").Range.Text) Then
If Trim(ActiveDocument.FormFields("Age").Result) = "" Then
'If ActiveDocument.Bookmarks("Age").Empty Then
GoTo Line4
Else: GoTo Line3
End If
Line4:
Application.ScreenUpdating = True
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
GoTo Line5
Line3:
If ActiveDocument.Bookmarks("Age").Range.Text < 16 Then
BMRange.Text = "Children - Under 16"
ElseIf ActiveDocument.Bookmarks("Age").Range.Text > 15 And ActiveDocument.Bookmarks("Age").Range.Text < 25 Then
BMRange.Text = "Transitional Youth - 16 to 24"
ElseIf ActiveDocument.Bookmarks("Age").Range.Text > 24 And ActiveDocument.Bookmarks("Age").Range.Text < 65 Then
BMRange.Text = "Adults - 25 to 64"
ElseIf ActiveDocument.Bookmarks("Age").Range.Text > 64 Then
BMRange.Text = "Seniors - 65+"
End If
'BMRange.Text = txtPHN.Text
'Re-insert the bookmark
ActiveDocument.Bookmarks.Add "testing", BMRange
'With ActiveDocument
' .Bookmarks("HeaderPHN").Range.Delete
' .Bookmarks("HeaderPHN").Range.Text = txtPHN.Text
Application.ScreenUpdating = True
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'End With
End If
Line5:
'Set objAge = Nothing
'Set objBirthDate = Nothing
End Sub
since the value of age is no longer in a form field, I am unsure how to have this insert continue.