Set an Expiration Date for Reusable Proposal Content

Once you have created a Library of Reusable Proposal Content,  you should next give some consideration to how you will keep track of updates to the content.  This is especially challenging if the Content is stored in a document.

Documents are often used to store Reusable Content,  because documents are portable, easy to share, and don’t require a big investment or infrastructure.  But keeping track of versions can be challenging.

A very common request that we hear from clients at Expedience, is to set an expiration date on Reusable Content, to prevent it from being used by mistake when a newer version is available.

In this post we will look  at how a few lines of code placed in the document’s ‘Open’ event can accomplish this.

Open the VBA Editor

VBA stands for “Visual Basic for Applications.”  This is a programming language that lets you create Macros for automating documents, such as the macros we have shown throughout this Blog.  For more on how to create macros, see our Blog post on how to Copy/Paste a Macro.

To get to the VBA Editor, you can select the ‘Visual Basic’ button on the Developer Tab:

 

If you do not see the Developer tab on your Word Ribbon, you can enable it buy going to File/Options/Customize Ribbon and checking the box next to Developer:

Or, you can simply use the shortcut:  Alt+F11

Once inside the VBA Editor, click on the menu item View and select ‘Project Explorer’:

This will show the documents that are currently opened.

Double Click on the Document that is to have an expiration date.  This will open a code window.

Select ‘Document’ from the dropdown menu. From the Event Dropdown on the Right select ‘Open’.

The code you enter here will be run whenever the Document is opened.

Edit the VBA Code

Here is our sample Expiration code:

To set the date, enter your own date inside the quotes:

 

To change the message that is received, edit the text inside the quotes:

To change the number of days before a warning is given, change the number ‘30’:

 

Because the document now contains a macro, you will need to save it either as a ‘.docm’ (Macro Enabled Document) or a ‘.dotm’ (Macro Enabled Template).

When the document is opened, you may receive a Macro warning:

Click ‘Enable Content.’  If the document has expired, you will see the following message:

The following is the Macro used in this post:

Private Sub Document_Open() ‘create a date variable Dim Exdate ‘Set the date Exdate = CDate(“May 25, 2015”) ‘if no date then do nothing If Exdate = “” Then  Exit Sub End If ‘check if date is later than today If Exdate < Now Then ‘soft warning   MsgBox “The content in this document expired on ” & Exdate & “ ‘check if date is occuring in next 30 days ElseIf (Exdate – 30) <= Now Then     MsgBox “The content in this document will expire on ” & Exdate & “.” End If End Sub