How to store and retrieve values in PowerPoint VBA for userforms?

Question

The aim is to save the properties of the Controls in a PowerPoint Userform after the presentation is closed.

I've tried this but that doesn't seem to work with PowerPoint

Private Sub DocumentBeforeClose(Cancel As Boolean)
    ' Save the value of the textbox to a document property
    ActivePresentation.CustomDocumentProperties.Add "MyTextBoxValue", False, msoPropertyTypeString, UserForm1.TextBox1.Value
End Sub

Answer 1

You have several issues here.

a) Using events in Powerpoint is a little bit tricky. If you really want to save the value(s) only in the moment when the presentation is closed, dig into it by reading for example http://youpresent.co.uk/powerpoint-application-events-in-vba/

b) Depending on the state of your userform, the values might be gone. If you for example close the form with the infamous Unload-command, the values are no longer available.

c) CustomDocumentProperties.Add will raise a runtime error when you write a property that already exists.

---

I would suggest instead of waiting until the document (or Powerpoint) is closed, you can use the QueryClose-event of the form that is triggered no matter how the form is closed. Put this code into the code behind module of the form:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    SaveProperty "MyTextBoxValue", Me.TextBox1.Value
    (add other properties)
End Sub

You can also put the code into the Initialize-Event of the form:

Private Sub UserForm_Initialize()
    Me.TextBox1.Value = GetProperty("MyTextboxValue")
End Sub

The following 2 routines will save/load property values. As those are completely independent of the form, you should put them into a regular module, but it works also if you put them into the code behind of the form:

Sub SaveProperty(propertyName As String, propertyValue As String)
    Dim p As DocumentProperty
    On Error Resume Next
    Set p = ActivePresentation.CustomDocumentProperties(propertyName)
    On Error GoTo 0
    
    If p Is Nothing Then
        ActivePresentation.CustomDocumentProperties.Add propertyName, False, msoPropertyTypeString, propertyValue
    Else
        p.Value = propertyValue
    End If
End Sub

Function GetProperty(propertyName As String) As String
    Dim p As DocumentProperty
    On Error Resume Next
    Set p = ActivePresentation.CustomDocumentProperties(propertyName)
    On Error GoTo 0
    
    If p Is Nothing Then Exit Function
    GetProperty = p.Value
End Function