Interactive Text message

[Ann]

I would like to build a dialog box for an value field that asks the user if
he is sure he added all of the payments between two dates.
A1: PolDate (Policy Aniversary Date)
A2: ValDate (Policy Valuation Date)
A3: Premiums paid between A1;A2
The user keys in 1/1/2007, 4/1/2007, 10,000, respectively. Upon pressing
enter on A3, a msg dialog box pops up asking, "did you include ALL payments
between 1/1/2007 and 4/1/2007?" if Yes, endsub, if no, return to A3 How do I
get the msg to read PolDate and Valdate?

 

[JLatham]

You need to use that worksheet's _Change() event to pull this off. What you
want to do is determine when a change takes place in A3 and then work from
that point. Code might look similar to what I show below. To put the code
in the proper place, choose that sheet and right-click on the sheet's name
tab and choose [View Code] from the list that appears. Copy and paste the
code into the module that is displayed and close the VB editor to begin using
it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then
Exit Sub ' didn't changed A3
End If
If IsEmpty(Target) Then
'was something in A3, but
'they [Del]eted it
'so it's now empty
Exit Sub
End If
If Not IsEmpty(Range("A1")) And _
Not IsEmpty(Range("A2")) Then
'A1 and A2 have values in them
'and we know A3 has something in it
If MsgBox("Did you include all payments between " & _
Range("A1") & " and " & Range("A2") & "?", vbYesNo, _
"Pmt Entry Check") <> vbYes Then
'responded NO or VB had a boo-boo
Range("A3").Select
End If
Else
'either A1 or A2 is empty and has no
'date in it. You can do whatever is needed
'to deal with that situation here.
End If
End Sub