Linking Date Picker to a Calendar Table

Vamosj · #1 11-20-2013, 03:03 PM

So I'm pretty good with Excel but still learning some of the in's and out's of Word when it comes to some of the more advanced formatting.

What I am trying to do is set up a form that allows the user to enter the date using a Date Picker and then I would like a table that is set up as a calendar to automatically format itself to adjust to that date.

I have attached a sample to show the two items involved.

Thanks in advance.

Also posted at
http://www.excelforum.com/word-forma...ml#post3484627

macropod · #2 11-22-2013, 12:13 AM

Hi Vamosj,

It's not apparent to me what relationship the two content controls have to the calendar that is to be displayed. Should it, for example, display whole months? What happens if the start & end dates are not in the same month? Why do you have a formfield (which should be used in the same document as content controls) in the table?

Vamosj · #3 11-22-2013, 10:59 AM

Quote:

Originally Posted by macropod

Hi Vamosj,

It's not apparent to me what relationship the two content controls have to the calendar that is to be displayed. Should it, for example, display whole months? What happens if the start & end dates are not in the same month? Why do you have a formfield (which should be used in the same document as content controls) in the table?

Hi Macro and thanks for the reply.

First off I linked the cross post in Excel Forum (forgot about that part), secondly, The correlation will be that the month will only be linked to the first date picked, so basically the starting month. This will give the office that handles the work assist requests time to deal with the immediate needs and then they can generate another sheet for any work that may lead into the next month.

We do this because the reports they send out are monthly. The form I'm creating is basically the one the requester would use to let us know how long they want it for, then we just adjust the form(s) to meet our reporting requirements.

So, if someone was to pick December 5th as the first part of the request date, then the Calendar would display "December 2013" and the calendar dates would adjust as needed to that month.

If it is not possible (I'm trying to stay away from Macro's) to obtain data between tables, then I would like to just create a dropdown menu in the calendar table with the months so the calendar can just adjust itself off of that.

I downloaded a single month calendar to try and review the coding used for that (see code below) and I just couldn't figure out how to adjust it to meet my needs.

Listed in Cell A3 of the calendar table

Code:

{IF{ DocVariable MonthStart\@ dddd}="Sunday" 1 ""}

The problem I'm having is trying to pull the data for what Month it is from the date picker (Top of sheet) or a dropdown menu & text form box (In the Calendar Table)

fumei · #4 11-22-2013, 05:33 PM

I am a bit confused by "date picker". It is a real date picker? And what is DocVariable? You say you do not want to use macros, but how does DocVariable get its value?

macropod · #5 11-22-2013, 06:44 PM

Quote:

Originally Posted by Vamosj

The correlation will be that the month will only be linked to the first date picked, so basically the starting month. This will give the office that handles the work assist requests time to deal with the immediate needs and then they can generate another sheet for any work that may lead into the next month.
...
So, if someone was to pick December 5th as the first part of the request date, then the Calendar would display "December 2013" and the calendar dates would adjust as needed to that month.

If it is not possible (I'm trying to stay away from Macro's) to obtain data between tables, then I would like to just create a dropdown menu in the calendar table with the months so the calendar can just adjust itself off of that.

Using content controls, you'll not be able to populate the table without a macro. Without content controls, you don't have access to a calendar content control...

A ContentControlOnExit macro to populate the calendar table from the first of your two calendar content controls (note the allocation of the "StartDate" title) is:

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False
Dim CCtrlDate As Date, i As Long, j As Long, k As Long, Rng As Range
If ContentControl.Title = "StartDate" Then
  CCtrlDate = CDate(ContentControl.Range.Text)
  i = CLng(CCtrlDate) - Format(CCtrlDate, "d") + 1
  j = DateDiff("d", CCtrlDate, DateAdd("m", 1, CCtrlDate))
  With ActiveDocument.Tables(2)
    Set Rng = .Range
    With Rng
      .Start = .Cells(9).Range.Start
      .Delete
    End With
    With .Range
      .Cells(1).Range.Text = "MONTH OF: " & Format(CCtrlDate, "MMMM, YYYY")
      For i = (i - 1) Mod 7 + 9 To .Cells.Count
        k = k + 1
        .Cells(i).Range.Text = k
        If k = j Then Exit For
      Next
    End With
  End With
End If
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

I still don't see where your second calendar content control comes into play. I'm not even sure whether you want the month populated from day one, or just from the date selected in the first calendar content control.

fumei · #6 11-22-2013, 11:53 PM

So "date picker" is a content control? And what is the DocVariable?

macropod · #7 11-23-2013, 12:45 AM

Quote:

Originally Posted by fumei

So "date picker" is a content control?

Yes. In fact, there are two of them in the first table. You'd need Word 2007 or later to access them.

Quote:

And what is the DocVariable?

Something Vamosj obtained a reference to somewhere else. As yet I don't see any use for it.

Vamosj · #8 11-25-2013, 09:20 AM

The DocVariable was used in the original template that I was trying to deconstruct and put into my own application. I have attached it here so you can see where I was getting my references from. The DocVariable is the Month listed at the top (Right Click -> Edit Field or Toggle Field Code).

The problem with this template is that it uses a Calendar tab in the Ribbon Toolbar. This looks like it is utilizing Macro's and on these goverment computers we have to enable this each and every time we open the document. I'm dealing with sailors so the majority of the time they miss the little "enable editing" button and then try to fill out the form without the macros enabled. Hence why I'm trying to stay away from using macros.

macropod · #9 11-25-2013, 11:03 AM

As I have already said, you cannot get this to work without macros - and that includes your DocVariable-based example.

So you'll need to either (a) train your users, (b) change their macro security settings, (c) store the template in what Word regards as a trusted location, or (d) give up on the idea of using the calendar content control and having your table populated as a calendar. I'd suggest (c). That way, the macro will run automatically without the users having to enable them and without compromising their general macro security settings.

Vamosj · #10 11-25-2013, 11:32 AM

Since I won't be able to train the users, or change their macro security settings, or give up on the idea. What I did come up with is I changed the Start Date to a form field with it running a macro on exit. This way it'll remind them that they need to have macro's on, and once they do that, it will automatically update each field in the Calendar to show the month from the Start Date.

Thanks for the time and we eventually answered my question of No, it isn't possible..

macropod · #11 11-25-2013, 11:34 AM

Quote:

Originally Posted by Vamosj

Since I won't be able to train the users, or change their macro security settings, or give up on the idea. What I did come up with is I changed the Start Date to a form field with it running a macro on exit.

If you're using formfields and foregoing the calendar content control, the lot can be done without macros...

Vamosj · #12 11-25-2013, 11:53 AM

But without a macro, wouldn't the fields down in the calendar have to be updated manually?

macropod · #13 11-25-2013, 01:19 PM

No, all you'd need to do is to set the input formfield's 'calculate on exit property' and ensure the calendar is in a Section with forms protection applied.

Vamosj · #14 11-26-2013, 12:11 PM

Finally able to bang my head against the wall to make things click on this, and now posting the finalized Calendar in case anyone has a need for something like this.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculating age of something from a Date Picker	BoringDavid	Word VBA	12	01-13-2014 02:40 AM
Default dates for a Date Picker	BoringDavid	Word VBA	2	09-11-2013 01:42 AM
Date Picker	Andy2011	Word VBA	4	11-24-2012 10:07 PM
word 2003 date picker	nashville	Word	16	04-06-2012 04:12 AM
Date picker	trintukaz	Excel	0	12-30-2011 12:42 AM