Custom Document Properties in Excel

Published on
8,163 Points
3 Endorsements
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.

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, _
    End With

    On Error GoTo 0
       Exit Sub

    If Err.Number = -2147467259 Then
        MsgBox "Custom DocumentProperty already exists"
        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
        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")
    End With

   On Error GoTo 0
   Exit Sub


    If Err.Number = 5 Then
        MsgBox "Custom DocumentProperty does not exist"
        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!

Author:Martin Liss
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free