Solved: Calculate future date based on selected date

[arcanedevice]

My document has two FormFields that are formatted for dates. The first is EffectiveDate with a popup calendar that opens on Entry. The second is ReviewDate which I want to set to default to 365 days from the selected date in EffectiveDate, but I can't seem to get the code right. I've been trying to use the AddDays function, but it keeps giving me an error.

Sub UpdateReviewDate()
Dim EffectiveDate As Date
EffectiveDate = ActiveDocument.Bookmarks("EffectiveDate").Range

Dim SetDate As Date
SetDate = EffectiveDate.AddDays(365)

Application.ScreenUpdating = False

With ActiveDocument
.Bookmarks("ReviewDate").Range = SetDate
End With
Application.ScreenUpdating = True
End Sub

[lucas]

This seems to work:
Sub UpdateReviewDate()
Dim EffectiveDate As Date
EffectiveDate = #2/12/1985#

Dim SetDate As Date
' SetDate = EffectiveDate.AddDays(365)
SetDate = EffectiveDate + 365
MsgBox SetDate
' Application.ScreenUpdating = False
'
' With ActiveDocument
' .Bookmarks("ReviewDate").Range = SetDate
' End With
' Application.ScreenUpdating = True
End Sub

[arcanedevice]

Thanks lucas

That's resolved the date, but in doing so it removes the field and I can't then change the date for that field if I need to.

[domfootwear]

Thanks lucas

That's resolved the date, but in doing so it removes the field and I can't then change the date for that field if I need to.
You can calcutate the day based on the active cell.

Your code here:

Sub UpdateReviewDate()
'Dim EffectiveDate As Date
'EffectiveDate = #2/12/1985#

Dim SetDate As Date
' SetDate = EffectiveDate.AddDays(365)
SetDate = ActiveCell + 365
MsgBox SetDate
' Application.ScreenUpdating = False
'
' With ActiveDocument
' .Bookmarks("ReviewDate").Range = SetDate
' End With
' Application.ScreenUpdating = True
End Sub

[lucas]

so this is a formfield bookmark?

[arcanedevice]

so this is a formfield bookmark?

Yes, I have two FormFields -one for the EffectiveDate and one for ReviewDate. The idea I've been working with is that the user picks an EffectiveDate using the pop-up calendar called on entry to the FormField, and once a date is selected, the ReviewDate FormField is to be updated with a date that is 365 days in the future, but can be changed.

[fumei]

The OP stated they were formfields: "My document has two FormFields that are formatted for dates. "

1. When using formfields it is better to use formfields...not bookmarks. Use .Result of the formfield. Using the Range of the bookmark deletes the bookmark (but not the formfield).

2. domfootwear, ActiveCell is not a Word object.
SetDate = ActiveCell + 365


The simplest way is an OnExit macro for the first date formfield, like this:
Sub UpdateOtherDate()
ActiveDocument.FormFields("Text2").Result = _
DateAdd("d", 365, ActiveDocument.FormFields("Text1").Result)
End Sub
The first formfield is Text1. Put the above as the OnExit macro. When you exit the formfield, the second (Text2) is updated with the .Result + 365.

For example, if you put 2010-01-09 into the first formfield, when you exit (to move to the second) the second has the result of: 2011-0109.

Demo attached.

[arcanedevice]

When using formfields it is better to use formfields...not bookmarks. Use .Result of the formfield. Using the Range of the bookmark deletes the bookmark (but not the formfield).

Thanks fumei, that has worked perfectly with all my ideas coming to fruition exactly as I had planned! I already had the code set up to run on exit from the first FormField, but just couldn't get it to work using bookmarks, so now I know why...

It's been a while since I've played with Word VBA, so the little differences between the products had me confused.

:friends: