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
'Set the date
Exdate = CDate("May 25, 2015")
'if no date then do nothing
If Exdate = "" Then
'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 & "."