Using calendar in form, calculate age & distribute form - 3 questi

[vblaradetrich1]

I have created a very simple form in Word using form fields. I have used a
simple macro for a pop-up calendar in Word, taken from the site
www.fontstuff.com/word/wordtut03a.htm. Now I would like to have the Calendar
pop-up when I click on a certain field. In the "Text Form Field Options"
there is a section that says "Run Macro on..." and there are two drop down
boxes with the headers Entry and Exit.
1. Do I simply select the Macro from the drop down list here or do I need to
do anything else to make the Calendar available when I click on the field?
2. I would like another field to show the age calculated using the calendar
from the previous field. Can someone please give me simple step-by-step
instructions on how to create this macro or does anyone already have a macro
for this?
3. I have been trying to distribute this form with the Macros & have been
unsuccessful. The Macros work on my computer but when I put the form on the
network drive others are not able to use the Macros. I know it is just a
matter of saving the Macro to the correct place but I can't figure out what I
am doing wrong. I read the article
word.mvps.org/faqs/Macrosvba/DistributeMacrosContent.htm but it mentions
putting the macro in other people's templates. I would like to just e-mail
the form to a user & have them be able to use the Macro. Please advise. As
you may be able to tell I am new to Macros & VBA so any help is appreciated.
Thanks.

 

[David Sisson]

1. Do I simply select the Macro from the drop down list here
Yes

2. I would like another field to show the age calculated using the calendar
from the previous field. Can someone please give me simple step-by-step
instructions on how to create this macro or does anyone already have a macro
for this?

Name the bookmarks in the formfields, MyBirthday and MyAge (or
anything you want really.)

Private Sub Calendar1_Click()
' Transfer date selected on calendar to the insertion
' insertion and close UserForm.
ActiveDocument.FormFields("MyBirthday").Result =
Format(Calendar1.Value, "dd mmmm yyyy")
ActiveDocument.FormFields("MyAge").Result =
Format(DateDiff("YYYY", Calendar1.Value, Date), "00")
Unload Me
End Sub

 

[vblaradetrich1]

Thanks David but I believe I worded my second question wrong. I am looking
for a formula to actually calculate the age from the date selected.
ex. User uses the calendar to select the DOB in one text field box & the age
is calculated & shown in another text field box.

Also, can you tell me how to ensure that the end user will be able to use
the form with all Macros intact if the end user will be receiving the form
via e-mail? I have tried with no success to send the form to others with the
Macro currently attached but it is not there when another user opens the form.

Thanks.

 

[Graham Mayor]

Assuming the date is entered in field Text1, the following macro run on exit
from text 1 will put the age in field Text2

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String

sDate = ActiveDocument.FormFields("Text1").Result
sDate = Format(sDate, "d m yy")
If sDate <> "" Then
If Not IsDate(sDate) Then
Debug.Print "Not a valid date!"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
If vDiff Mod 12 >= 6 Then
sResult = CStr(CInt((vDiff / 12)) + 1)
MsgBox "Difference in years is almost " & sResult
Else
sResult = CStr(CInt(vDiff / 12))
ActiveDocument.FormFields("Text2").Result = sResult
End If

End If
End If
End Sub

You should also be able to do this with calculated fields, albeit it is far
more complicated and you will need the samples from
www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

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


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

 

[David Sisson]

Thanks David but I believe I worded my second question wrong. I am looking
for a formula to actually calculate the age from the date selected.
ex. User uses the calendar to select the DOB in one text field box & the age
is calculated & shown in another text field box.

Did you try it? It works just that way on my 'puter.

Also, can you tell me how to ensure that the end user will be able to use
the form with all Macros intact if the end user will be receiving the form
via e-mail? I have tried with no success to send the form to others with the
Macro currently attached but it is not there when another user opens the form.

'It is just not there..." What do you mean? Are the macros stripped
for the doc/dot? Test send the doc to another email you have access
to and see what happens.
I've have to distrubute dot's before and I had to make exacting
directions before they could make it work.

 

[Russ]

One problem of sending .doc files with macros is virus security issues:
There's always Google:

 

[aneasiertomorrow]

Hi Graham

Thanks for this code snippet - just what I needed. Almost. Would it be
possible to have it display years and months? Sorry, I'm new to VBA and the
wopr site is down.

Many thanks for your help,
Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there

 

[Graham Mayor]

Answered in your post in another forum.

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


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