Formula in a Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny - 05 Apr 2007 15:28 GMT

I would like to create an If statement in a macro to subtract one date from
another date located on my form and if the result is greater then 3, a
userform (which I already created) pops up prompting a few questions. The
two dates are located on a form in the text fields as Text19 and Text20.

I haven't got a clue on how to incorporate a forumla in a macro let alone
subtract two dates to determine if the numerical value is greater than 3.

Thanks.

Reply to this Message

Greg Maxey - 05 Apr 2007 17:18 GMT

Subject to some possible error handling, this might get you on track:

Sub OnExitTxt20()
Dim dtg1 As Date
Dim dtg2 As Date
Dim oBMs As Bookmarks
Dim dtgDiff As Long
Set oBMs = ActiveDocument.Bookmarks
dtg1 = oBMs("Text19").Range.FormFields(1).Result
dtg2 = oBMs("Text20").Range.FormFields(1).Result
If IsDate(dtg1) And IsDate(dtg2) Then
dtgDiff = CLng(DateDiff("d", dtg1, dtg2))
End If
If dtgDiff > 3 Then
'Fire your UserForm
End If
End Sub

> I would like to create an If statement in a macro to subtract one date from
> another date located on my form and if the result is greater then 3, a
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message