When developing Excel applications, it's a common need to be able to store settings or non-sheet data so that they are available the next time you open Excel. Some examples of data you might want to store are a Yes/No value pertaining to the user seeing a certain message, a user-selected highlight color, or a count of how many times the user has opened the workbook.
There are several common ways to do that including ini files (text files with a recognized structure), regular text files, databases and the Registry, however some of those methods have potential problems with permissions or the processing time involved with opening and closing external files.
One less-known method that doesn't have those problems is Custom Document Properties. These are Just like the built-in properties stored in Office documents (like those shown in the above picture) except that these are ones that you can create, modify or delete.
A Practical Example
I recently created an Excel application where I wanted to display an informational message when a certain action was performed but I realized that since the action would be performed many times that the message would soon become annoying. Because of that I needed to give the user a way to turn the message off or on as needed and to remember that choice the next time the workbook was opened, and I decided on a Custom Document Property.
The first step was to create the property and that was accomplished with this code.
Sub CreateCDP() On Error GoTo CreateCDP_Error With ActiveWorkbook.CustomDocumentProperties .Add Name:="Show Message", _ LinkToContent:=False, _ Type:=msoPropertyTypeString, _ Value:="Yes" End With On Error GoTo 0 Exit Sub CreateCDP_Error: If Err.Number = -2147467259 Then MsgBox "Custom DocumentProperty already exists" Else MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateCDP of Module Module1" End If End Sub
That created a property called "Show Message" with an initial value of "Yes". In other situations that value could could have been a number or other variable types. See this article for a complete description. The reason for the error checking is that an error will result if the property already exists.
To use the property I created a macro similar to this one.
Sub ShowAgain() If ActiveWorkbook.CustomDocumentProperties("Show Message") = "Yes" Then If vbCancel = MsgBox("Some long, possibly annoying, message." _ & vbCrLf & vbCrLf & "(Click 'Cancel' if you no longer wish to see this message)", vbOKCancel) Then HideMessage End If End If End Sub
That refers to the HideMessage macro which changes the value of the property to "No". It looks like the following.
Sub HideMessage() With ActiveWorkbook.CustomDocumentProperties("Show Message") .Value = "No" End With End Sub
I also created these two companion macros.
Sub ShowMessage() With ActiveWorkbook.CustomDocumentProperties("Show Message") .Value = "Yes" End With End Sub
Sub RemoveCDP() On Error GoTo RemoveCDP_Error With ActiveWorkbook.CustomDocumentProperties("Show Message") .Delete End With On Error GoTo 0 Exit Sub RemoveCDP_Error: If Err.Number = 5 Then MsgBox "Custom DocumentProperty does not exist" Else MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RemoveCDP of Module Module1" End If End Sub
ShowMessage will turn the message on again, and RemoveCDP deletes the custom property. It has error checking because an error will occur if the property already exists.
I've attached this workbook which demonstrates the use of the custom property. All the code is in Module1.
If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!