Save userform value for next time

Question

I want users to enter a path into a textbox in a settings page and use this path-string each time they load the userform. The users should not have to enter the path each time they open the userform.

I can't find anything on the internet about saving input values of userforms for the next time, they are loaded.

Answer 1

If you feel adventurous, here is a solution that will give you pleasure. Start with a standard code module. By default it will be called Module1. Place the following two procedures in that module. It doesn't matter what you call the module, but be sure not to use the ThisWorkbook code module, nor any of the code modules named after the worksheets.

The function 'SavedDataFileName' is the place where you declare the location where you want to store the data to be remembered. You can use any location you like. the code specifies a location like C:\Users\[Your Name]. You can also change the file name. The point is that all this is stored in one place from where it is retrieved when the file is read and when it is written.

Option Explicit
    
Function SavedDataFileName() As String
    ' create this as a function to be called by various parts of your code
    ' so that you don't have to repeat it in many places in case of future change
    
    SavedDataFileName = Environ("USERPROFILE") & "\SavedPath.txt"
End Function

The next function reads the text file just specified. Actually, this is code I had ready. Therefore it has the capability to read many data. You only want to read one - the path. It will do that.

Function TextFile(Ffn As String, _
                  MaxLines As Integer) As String()
    ' 17 Oct 2016
    
    Dim Fun() As String                             ' Function return
    Dim i As Integer
    Dim Fid As Integer                              ' File ID
    
    If Len(Dir(Ffn)) Then
        ReDim Fun(MaxLines)                         ' arbitrary maximum
        Fid = FreeFile()
        Open Ffn For Input As #Fid
        While Not EOF(Fid)
            Line Input #Fid, Fun(i)
            Fun(i) = Trim(Fun(i))
            i = i + 1
        Wend
        Close #Fid
        ReDim Preserve Fun(i - 1)
    End If
    TextFile = Fun
End Function

Now please go to the code sheet of your form on which you want the data to be preserved. All the following procedures must be on that form. Don't install them anywhere else. They can only work when installed on that particular code sheet.

The first procedure runs when the form is initialised, meaning when it is first created - on startup.

Option Explicit

Private Sub UserForm_Initialize()
    ' 13 May 2017
    
    Dim SavedData() As String
    
    On Error GoTo EndRetrieval
    SavedData = TextFile(SavedDataFileName, 10)
    TextBox1.Text = SavedData(0)
    ' you can pre-load more controls in your form here
EndRetrieval:    
End Sub

In this sub the text file is opened and data imported from it. A maximum of 10 data are expected. You can set this number higher or lower. The higher you set it the more memory space will temporarily be used. You only need 1 data item. That will have the index number 0 (zero), and it is assigned to TextBox1. You can change that, of course. If an error is encountered nothing will be done and the text box remains blank.

The next procedure will run whenever you exit from TextBox1. Of course, you can change its name to refer to another text box. Its purpose is to ensure that the textbox contains a valid path name. If the user enters something that isn't a valid name he will be told so.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ' 13 May 2017
    
    Dim Ffn As String                       ' Full file name
    
    Ffn = Trim(TextBox1.Text)
    On Error Resume Next
    ' For testing MsgMe is a sub containing a MsgBox.
    ' MsgMe Dir(Ffn & "\", vbDirectory) & ", " & Len(Dir(Ffn & "\", vbDirectory))
    Cancel = (Len(Ffn) = 0) Or (Len(Dir(Ffn & "\", vbDirectory)) = 0)
    If Not Cancel Then Cancel = CBool(Err.Number)    ' in case Dir(Ffn) caused an error

    If Cancel Then
        MsgBox "The path you entered isn't valid." & vbCr & _
               "Please enter a valid path."
    Else
        TextBox1.Text = Ffn                ' removed leading/trailing blanks
    End If
End Sub

The final procedure runs when the form is closed. It write the current setting of TextBox1 to the text file from where it will be retrieved when the form is next loaded.

Private Sub UserForm_Terminate()
    ' 12 May 2017
    
    Open SavedDataFileName For Output As #1
    Print #1, TextBox1.Text
    ' you can write more data to be  remembered here
    Close #1
End Sub

This procedure writes only a single line to the text file. If you want to save more items just print more values to the file. Observe that the quality of the data saved isn't checked. That was done after it was entered. If the user managed to smuggle a faulty path into the text box that faulty path will come back to haunt him the next morning.

Answer 2

What about a custom doc property?

In addition to @Ralph 's valid comment I'd like to demonstrate an often unconsidered way: storing the value directly into a custom document property (CDP):

UserForm code part (1) - Event procedures

• UserForm_Activate() just redisplays the stored value at any activation of the userform.
• TextBox1_Exit() stores any Path value change immediately into the CDP MyPath

Option Explicit

Private Sub UserForm_Activate()
'Purp.: Display (stored) MyPath value on activation
    'MsgBox "Current value of MyPath = " & MyPath
    'display current value in textbox
    Me.TextBox1 = MyPath
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    '(only for comparison)
    Dim OldPath As String
    OldPath = MyPath
    '~~~~~~~~~~~~~~~~~~~~~~~~
    'store changed Path value
    '~~~~~~~~~~~~~~~~~~~~~~~~
    MyPath = Me.TextBox1
    MsgBox "Changed MyPath " & vbNewLine & _
           "from: " & OldPath & vbNewLine & _
           "to:   " & MyPath
End Sub

Userform code part (2) - CDP-related

As any UserForm represents only a special sort of class, you could even use Get/Let properties within the Userform code module together with a boolean function checking validity.

The following code doesn't intend to show a best-of, but intends only to show a further way leading to Rome.

'Get-/Let-Properties
Private Property Get MyPath() As Variant
    Const STOREDPATH As String = "MyPath"
    Dim cdps As DocumentProperties
    Set cdps = ThisWorkbook.CustomDocumentProperties
    If CDPExists(STOREDPATH) Then MyPath = cdps.Item(STOREDPATH)
End Property

Private Property Let MyPath(ByVal CDPValue)
    Const STOREDPATH As String = "MyPath"
    Dim cdps As DocumentProperties
    Set cdps = ThisWorkbook.CustomDocumentProperties

    If Not CDPExists(STOREDPATH) Then
        cdps.Add Name:=STOREDPATH, LinkToContent:=False, Type:=msoPropertyTypeString, Value:=CDPValue
    Else
        cdps.Item(STOREDPATH) = CDPValue
    End If
End Property

Private Function CDPExists(CDPName As String) As Boolean
' Purp.: return True|False if Custom Document Property (CDP) name exists
' Meth.: loop thru CustomDocumentProperties and check for existing sCDPName parameter
' Site : https://stackoverflow.com/questions/41766268/check-if-builtindocumentproperty-is-set-without-error-trapping
    Dim cdps As DocumentProperties
    Set cdps = ThisWorkbook.CustomDocumentProperties
    Dim boo  As Boolean                               ' boolean value showing element exists
    Dim cdp  As DocumentProperty                      ' element of CustomDocumentProperties Collection
    For Each cdp In cdps
        If LCase(cdp.Name) = LCase(CDPName) Then
            boo = True                                ' heureka
            Exit For                                  ' exit loop
        End If
    Next
    CDPExists = boo                                   ' return value to function
End Function

Related link

Similar to the last function checking custom doc props, a related elder post treats Check if built-in doc property is set without error trapping

Answer 3

I recommend using SaveSetting appname, section, key, setting to store data and GetSetting appname , section, key [, default ] to retrieve values.

For Your example:

Private Sub UserForm_Terminate()
    '
    ' Save Setting to the windows registry
    ' usually values are stored at the following path:
    ' Computer\HKEY_USERS\{user-guid}\Software\VB and VBA Program Settings
    '
    SaveSetting "YourApplication", "UserFormXYZ", "TextBox1", TextBox1.Text
End Sub

Private Sub UserForm_Initialize()
    dim defaultPath   As String
    dim userPath      As String
    ' set defaultPath as you require

    ' get settings from Registry
    userPath = GetSetting("YourApplication", "UserFormXYZ", "TextBox1", defaultPath)
    TextBox1.Text = userPath
End Sub

Answer 4

One way is this...

Declare a Public variable on a Standard Module like this...

Public Path As String

Then on UserForm Module have the following code for TextBox AfterUpdate event...

Private Sub TextBox1_AfterUpdate()
Path = TextBox1.Value
End Sub

The Path variable will hold the TextBox1.Value for the current session and User won't need to provide the Path again in the TextBox1.