Custom Document Properties in Excel

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
Updated:
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, _
            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.

CDP.xlsm


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!

4
5,499 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.