Sub ShowMessage() With ActiveWorkbook.CustomDocumentProperties("Show Message"). I also created these two companion macros. Sub HideMessage() With ActiveWorkbook.CustomDocumentProperties("Show Message"). That refers to the HideMessage macro which changes the value of the property to "No". 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 To use the property I created a macro similar to this one. The reason for the error checking is that an error will result if the property already exists. See this article for a complete description. In other situations that value could could have been a number or other variable types.
That created a property called "Show Message" with an initial value of "Yes". 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 Sub CreateCDP() On Error GoTo CreateCDP_Error With ActiveWorkbook.CustomDocumentProperties. The first step was to create the property and that was accomplished with this code. 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. 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. 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. One less-known method that doesn't have those problems is Custom Document Properties. 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. 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. 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.
0 kommentar(er)
