Solved: Using Calendar Control for Office Applications

[Dave T]

Hello All,

I have recently purchased a Holy Macro book called "Office VBA - Macros you can use today (http://www.mrexcel.com/officevba.html)".

My apologies for cross posting this message, but as it the book was published by Holy Macro (MrExcel) I thought I should first ask my question in the MrExcel message board (link (http://www.mrexcel.com/forum/showthread.php?t=532824)).
However as VBA Express has a dedicated Word forum, I felt this might be a more appropriate site to pose these questions.

I have been looking at the topic/macro called "Using Calendar Control for Office Applications" which has a non ActiveX calendar.

Due to possible copyright issues I did not want to post any of the code in a web forum.

I would like to be able to use this within a Word document that has been set up as a 'Form' using form fields.

I have added the "ShowForm" macro to the 'Run macro on entry box' within the 'Text Form Field Options', which pops up the calendar when an appropriate field is selected.

There are two things I have yet to work out...

How to get the selected calendar date to be returned into the selected form field. The macro works great when in a normal document but not within a protected form.
When using the calendar on a normal document the date is returned in the default format of d/mm/yyyy and I would like to be able to modify this so the date can be returned in another format e.g. dd-MMM-yy or dddd, dd MMM yyyy, etc.I have been looking at various web sites that look at calendars…
Graham Mayor
Use a pop-up calendar to select and insert a date
http://www.gmayor.com/popup_calendar.htm

Martin Green
A Pop-up Calendar for Word - Part 1: A Simple Calendar
http://www.fontstuff.com/word/wordtut03a.htm
A Pop-up Calendar for Word - Part 2: Customizing the Calendar
http://www.fontstuff.com/word/wordtut03b.htm
I Want the Calendar to Open Automatically When the User Enters a Form Field
http://www.fontstuff.com/mailbag/qword01.htm

When the calendar is used on a normal document and the user clicks OK the date selected on the calendar is returned to the insertion point selected on the document.

Both Graham and Martin mention that extra coding needs to be done to the macro to enable it to be used with a protected form, one even suggests recreating another version of the initial code and modifying this for use within a form.

The calendar UserForm has an OK button, which when selected returns the selected date, but this does no work when used on a protected ‘form’ section of a document.
I was wondering if it was possible to just add another button the UserForm (called something like ‘Form OK’) that would enable the selected date to be returned to a form field within the protected section.

My other question is whether the macro would need to be coded with the type of date format returned or would this be done by selecting the ‘Text Form Field Options’ to ‘Date’ and creating a date format.
If this was possible then the date returned would not require any macro coding.

Any help or comments would be appreciated.
Regards,
Dave T

[gmaxey]

You should be able to write the calendar control value to a text formfield in the protected document. For example if you have a formfield named "FFforDateInsert" and include the following code in your UF OK click event:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim pDate As Date
pDate = Date 'replace Date with the calendar control result
ActiveDocument.FormFields("FFforDateInsert").Result = CStr(pDate)
End Sub

[Dave T]

Hello Greg,

Thanks for the quick reply,

Tried what you suggested and it works OK.
Also found out that the returned date takes on the date format you have set.

I noted that you have labelled the formfield 'FFforDateInsert'...
I thought it was not possible to have more than one bookmark with the same name i.e. they had to be unique???
If this is the case is there a way of Word knowing the field you have just selected and returning the date to that specific field. This would mean not having to name any bookmarks.

Thanks again Greg.

Regards,
Dave T

[Dave T]

Hello All,

Whenever I enter a Text Form Field the default bookmark of Text1, Text2 and so on is also added.

What would I need to add to Greg's code to enable it to be used on multiple form fields.

I have several Text Form Fields that I want to use the calendar for, so would I have to keep adding and modifying the line:
ActiveDocument.FormFields("FFforDateInsert").Result = CStr(pDate)
over and over again with the bookmark changed for each entry e.g.
ActiveDocument.FormFields("Text1").Result = CStr(pDate)
ActiveDocument.FormFields("Text2").Result = CStr(pDate)

Is the bookmark actually required ??
Is each Form Field uniquely identifiable on its own or does it require the bookmark to be added to make it unique ??

Is it possible to call the calendar form on various fields and add the date without having to add or edit the bookmark for each field ??

Regards,
Dave T

[gmaxey]

David,

Sorry. I thought (I know) that I replied here yesterday. For some reason it wasn't posteed.

You can execute code on the selected formfield using:

If Selection.Bookmarks.Count > 0 And Selection.FormFields.Count = 0 Then
Selection.Bookmarks(1).Range.FormFields(1).Result = CStr(pDate)
End If

Typically the formfield name and bookmark are one in the same. Yes, when you enter a formfield it is named (and bookmarked) "Text1", "Text2" etc. but you don't have to live with that name. You can rename it whatever you like. In this case you can stick with the default because you won't really need to know the name.

[Dave T]

Hello Greg,

I am having a blank moment and cannot figure how to add the code within your last message to that within your first reply.

Thanks for helping me out.

Regards,
Dave T

[gmaxey]

Try this instead:
Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim pDate As Date
Dim oFF As FormField
pDate = Date 'replace Date with the calendar control result
If Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0 Then
Set oFF = ActiveDocument.FormFields(Selection.Bookmarks(1).Name)
oFF.Result = CStr(pDate)
End If
End Sub

[Dave T]

Thank you very much Greg,

You put me on the right path.
Your code kept putting in today's date, then after a bit of playing around with it I found I just needed to modify your code slightly.



Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim pDate As Date
Dim oFF As FormField
pDate = Me.Value 'replace Date with the calendar control result
If Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0 Then
Set oFF = ActiveDocument.FormFields(Selection.Bookmarks(1).Name)
oFF.Result = CStr(pDate)
End If
End Sub


I really appreciated your help.

Regards,
Dave T

[gmaxey]

You're welcome.