Save variable with CustomDocumentProperties

[Formula11]

One of the ways to store a variable when closing and re-opening workbook is with CustomDocumentProperties.
The variable changes depending on selection (one of 3 values).
Selection is from userform option buttons.

Below code works only for 1 selection and then have to exit form.

How can it work for alternating selection when userform is still on.

Private Sub option_button_Full_Click()
    If Err Then
        Err.Clear
        With ActiveWorkbook.CustomDocumentProperties("my_property")
            .Delete
        End With
    End If
    With ActiveWorkbook.CustomDocumentProperties
        .Add Name:="my_property", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="value_1"
    End With
    MsgBox ThisWorkbook.CustomDocumentProperties("my_property").Value
End Sub

Private Sub option_button_Partial_Click()
    If Err Then
        Err.Clear
        With ActiveWorkbook.CustomDocumentProperties("my_property")
            .Delete
        End With
    End If
        With ActiveWorkbook.CustomDocumentProperties
            .Add Name:="my_property", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="value_2"
        End With
    MsgBox ThisWorkbook.CustomDocumentProperties("my_property").Value
End Sub

Private Sub option_button_Manual_Click()
    If Err Then
        Err.Clear
        With ActiveWorkbook.CustomDocumentProperties("my_property")
            .Delete
        End With
    End If
        With ActiveWorkbook.CustomDocumentProperties
            .Add Name:="my_property", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="value_3"
        End With
    MsgBox ThisWorkbook.CustomDocumentProperties("my_property").Value
End Sub

 

[dmt32]

Another way to store control values is in a worksheet (which can be hidden) & use this to recall their last set values when userform is loaded.
Approach also has advantage of being able to store settings for multiple users of same userform if needed.

Dave

 

[Formula11]

OK thanks, may try this.

 

[dmt32]

OK thanks, may try this.

Let me see if I can save you the time

Place ALL following code is a STANDARD module

Enum UFSettingsAction
    ufReturnSettings = 1
    ufSaveSettings
    ufClearSettings
End Enum

Sub ControlSettings(ByVal Form As Object, ByVal Action As UFSettingsAction)
    Dim ws                  As Worksheet
    Dim r                   As Long, c As Long
    Dim m                   As Variant, UsersName As Variant
    Dim HideSettingsSheet   As XlSheetVisibility
    Dim SaveSettingsOnExit  As Boolean
    Dim ctrl                As Control
    
    On Error GoTo myerror
    
    '--------------------------------------------------------------------------------------------
    '                                       SETTINGS
    '--------------------------------------------------------------------------------------------
    
    'hide settings worksheet
    HideSettingsSheet = xlSheetVeryHidden
    
    'save the workbook when userform is closed
    SaveSettingsOnExit = True
    
    '-------------------------------------------------------------------------------------------
    'Note:Code does not save multi-selection listbox values
    '-------------------------------------------------------------------------------------------
    'get users network name
    UsersName = Environ("USERNAME")
    
    'create sheet to store userform control values
     If Not Evaluate("ISREF('" & Form.Name & "'!A1)") Then
        Set ws = Worksheets.Add(After:=Worksheets(Sheets.Count))
        'name sheet
        ws.Name = Form.Name
        'add header
        ws.Cells(1, 1).Value = "Control Name / UserName"
    Else
        'sheet exists
        Set ws = Worksheets(Form.Name)
    End If
    
    'hide sheet
    ws.Visible = HideSettingsSheet
    
    'see if UserName exists
    m = Application.Match(UsersName, ws.Rows(1), 0)
    'get column for usersname
    c = IIf(IsError(m), ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1, CLng(m))
    'if missing, add to list
    If IsError(m) Then ws.Cells(1, c).Value = UsersName
    'hide sheet
    
    'loop controls
    For Each ctrl In Form.Controls
        Select Case TypeName(ctrl)
            'check these controls only
            Case "TextBox", "ComboBox", "ListBox", "OptionButton", "CheckBox", "ToggleButton"
                'see if control name exists
                m = Application.Match(ctrl.Name, ws.Columns(1), 0)
                'get the row for control name
                r = IIf(IsError(m), ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, CLng(m))
                'if missing, add name to list
                If IsError(m) Then ws.Cells(r, 1).Value = ctrl.Name
                
                If Action = ufReturnSettings Then
                    'return control value
                    ctrl.Value = IIf(VarType(ctrl) = vbBoolean And Len(ws.Cells(r, c).Value) = 0, False, _
                    ws.Cells(r, c).Value)
                ElseIf Action = ufSaveSettings Then
                    'save control value
                    ws.Cells(r, c).Value = ctrl.Value
                Else
                    'clear control value
                    ws.Cells(r, c).Value = ""
                End If
        End Select
nextcontrol:
    Next ctrl
    
    'save settings
    If SaveSettingsOnExit And Action = ufSaveSettings Then ThisWorkbook.Save
    
    ws.UsedRange.Columns.AutoFit
    
myerror:
    If Err <> 0 Then MsgBox (Error(Err)) , 48, "Error"
    If Err.Number = 380 Then Resume nextcontrol
End Sub

Above code can be used for any userform for your project

to use - place following the codes in the userform(s) events shown below

Private Sub UserForm_Initialize()
    ControlSettings Me, ufReturnSettings
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ControlSettings Me, ufSaveSettings
End Sub

when called, the code creates a worksheet listing all the following controls "TextBox", "ComboBox", "ListBox", "OptionButton", "CheckBox", "ToggleButton"
Settings for these controls will be stored against each user of the userform & recalled when it is loaded.

Hope Helpful

Dave

 

[Formula11]

Thanks for preparing dmt32, this will help a lot.

 

[dmt32]

Thanks for preparing dmt32, this will help a lot.

Hi most welcome - I wrote code sometime ago for another here & just updated it a little

Appreciate your feedback

Dave