Linking 2 dates

deodi · #1 06-07-2016, 05:14 AM

I have a document that I have various dates that needs to be selected by the user (will be protected so no editing on their behalf). I want to be able to choose 1 date, it should then pull through to other places. I got the "publish date" in quick parts, so I get that to work. However, I have another set of dates that should be a year later (expiry date) and this should be automatically populated by choosing the first date (publish date). As Quick Parts only have 1 date one I can't seem to create another date nor get the "fields" to work as read in other posts.

Please could you advise me on how to get the date to automatically populate by selecting the "publish date".

gmayor · #2 06-07-2016, 06:29 AM

See http://www.gmayor.com/insert_a_date_...than_today.htm and in particular the link there to Paul's excellent work on date calculations, which you can download and copy the relevant structures to your document.

deodi · #3 06-07-2016, 11:57 PM

I am still struggling to get the 'formula' to work. I want to be able to choose a date on the document (so using a drop down list) and then the expiry date should automatically populate to be a year from now less 1 day. Could you write the exact code (or explain what each line item is for) to be able to do this?

I can send you the file in order to assist?

gmayor · #4 06-08-2016, 02:37 AM

Attach the file (or you can send it to me at supportATgmayor.com, but if you do so ensure that your forum username is in the message subject or it will be discarded).

deodi · #5 06-08-2016, 04:14 AM

Thank you gmayor! Attached find the document.

gmayor · #6 06-08-2016, 06:04 AM

OK. Add the following to the ThisDocument module of the document

Code:

Option Explicit

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    If ContentControl.Title = "Issue Date" Then
        If Not ContentControl.Range.Text = ContentControl.PlaceholderText Then
            ActiveDocument.SelectContentControlsByTitle("Expiry Date").Item(1).Range.Text = _
            DateAdd("m", 12, CDate(ContentControl.Range.Text))
        End If
    End If
End Sub

Save the document as a macro enabled template and create new documents from it. As you tab out of the first date field the second one reflects 12 added months.

deodi · #7 06-08-2016, 07:20 AM

I went into the document I sent you and clicked on the macros button. There was a Macros1 on the list. I clicked edit and inserted the code you provided and exited the Visual Basic. I then saved the document as a Macro Enabled Template. I then clicked on the template and chose the date on issue date, but the expiry date did not change.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to change logo /dates in numerous word documents without changing each one individually?	newi1	Word VBA	3	04-22-2016 07:02 PM
Autofill dates - how can I get the same date and consecutive dates?	Exhale	Excel	3	04-05-2016 03:11 AM
Linking dates in Headers and Footers	lenoble22	Word	1	01-16-2014 12:20 PM
How to change dates automatically	PaperBuster	Word	5	09-24-2012 09:31 PM
How to change number format of dates	ketanco	Project	1	09-11-2012 02:34 PM