Visual Basic for Applications/Backup Text Boxes on Close

Summary

This VBA code is written for Microsoft Excel but is easily adapted to other applications in the MS Office set. It saves all of the text from a user form's text boxes in a log file whenever the form is closed. Then later, on re-opening the form, or at any other time, the user can fill the boxes with the most recent saved text.

The VBA Code

• The code needs a user form called Userform1, two text boxes, TextBox1 and TextBox2, and a command button called CommandButton1. Set the UserForm1 property ShowModal to false for convenient study. Copy the code below into the three respective modules and save the workbook with an xlsm file suffix.
• Any code that is found in text boxes will be saved when the user form closes. This includes inadvertent closure of the user form or the deliberate closing of the workbook. It does not of course protect against the effect of power failures. The saving of data happens without intervention, so may need consideration if the storage of sensitive data is to be avoided.
• The log file is called SavedText.txt, and will be found in the same folder as the workbook. If a log file of that name is not found, then it will be made by the code for use. The log file has only two fields, the text box name and the string contents found in it. The comma separator was avoided in favor of the less likely encountered string >Break<.
• The saving function runs from the UserForm_QueryClose event. SaveTextBoxes() makes a log string in a userform controls loop, then exports the string via WriteToFile().
• WriteToFile() makes a log file if it does not exist, but otherwise overwrites any text that it finds, so that only the most recently saved session will be found there. Users who employ the logging procedure elsewhere should note that an extra Cr and Lf are stored at the end of logged entries, and might need consideration.
• RestoreTextBoxes() runs only by pressing CommandButton1, so the user chooses whether or not to insert text. GetAllFileText() imports all of the log file's contents at once with the file retaining contents until it is next overwritten. The string is split twice, once to break it into lines, that is, one for each text box record, and then again to break each record into its two fields for matching control names in the main transfer loop.

Code Changes

8 March 2019: Changed data separator from comma to other, in Standard Module

For the ThisWorkbook Module

'...............................................
' Notes: Code needs a user form named UserForm1,
' with two text boxes, TextBox1 and Textbox2,
' and a command button with name CommandButton1.
' Set UserForm1 property ShowModal to False 
'...............................................

Private Sub Workbook_Open()
    'Runs on opening the workbook
   
    Load UserForm1
    UserForm1.Show

End Sub

For the Userform1 Module

Private Sub CommandButton1_Click()
    ' Restores saved textbox text
    ' after reopening the user form
    
    ' restores textbox text from file
    RestoreTextBoxes
    
    'set insertion point to TextBox1
    With TextBox1
        .SelStart = Len(.Value) 'to end of text
        .SelLength = 0          'just insertion
        .SetFocus
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Runs before closing the userform
    ' Used here to save textbox values in a log file

    SaveTextBoxes
    
End Sub

For the Standard Module

Option Explicit

Sub SaveTextBoxes()
    ' Saves values from user form text boxes to a log file
    ' Data is never lost while log file exists
    ' Runs in the UserForm_QueryClose() event at all times.
        
    Dim oForm As UserForm, oCont As Control, sStringOut As String
    Dim bCont As Boolean, sPath As String, sLogPath As String
    Dim sType As String
    
    Set oForm = UserForm1
    sPath = Application.ThisWorkbook.Path
    sLogPath = sPath & "\" & "SavedText.txt" 'log file address
    sType = "TextBox"
    
    'step through the form controls to find the textboxes
    For Each oCont In oForm.Controls
        If TypeName(oCont) = sType Then
            sStringOut = sStringOut & oCont.Name & ">Break<" & oCont.Value & vbCrLf
        End If
    Next oCont
    
    'remove tailend Cr and Lf
    sStringOut = Left$(sStringOut, Len(sStringOut) - 2)
    
    'send textbox string to the log file
    WriteToFile sStringOut, sLogPath
        
    'release object variables
    Set oForm = Nothing
    Set oCont = Nothing

End Sub

Function WriteToFile(ByVal sIn As String, ByVal sPath As String) As Boolean
    ' REPLACES all content of a text file with parameter string
    ' Makes the file if it does not exist
    ' Assumes that all formatting is already in sIn
    ' Note that this log file will add Cr and Lf to the stored string
    
    Dim Number As Integer
    
    Number = FreeFile 'Get a file number
    
    'write string to file
    Open sPath For Output As #Number
    Print #Number, sIn
    Close #Number

    WriteToFile = True
    
End Function

Sub RestoreTextBoxes()
    ' Restores saved values to user form text boxes.
    ' Data is never lost while log file exists.
    ' Runs when CommandButton1 is pressed
        
    Dim oCont As Control, oForm As UserForm
    Dim vA As Variant, vB As Variant, sRet As String
    Dim sPath As String, sLogPath As String, nC As Long
    
    Set oForm = UserForm1
    sPath = Application.ThisWorkbook.Path
    sLogPath = sPath & "\" & "SavedText.txt"
    
    'get text from the log file
    GetAllFileText sLogPath, sRet
    
    'remove the extra Cr and Lf added by the log file
    sRet = Left(sRet, Len(sRet) - 2)
    
    'step through controls to match up text
    vA = Split(sRet, vbCrLf)
    For nC = LBound(vA, 1) To UBound(vA, 1)
        'MsgBox Asc(vA(nC))
        vB = Split(vA(nC), ">Break<")
            For Each oCont In oForm.Controls
                If oCont.Name = vB(0) Then
                    oCont.Value = vB(1)
                End If
            Next oCont
    Next nC
   
    'release object variables
    Set oForm = Nothing
    Set oCont = Nothing

End Sub

Function GetAllFileText(ByVal sPath As String, sRet As String) As Boolean
    ' Returns entire log file text in sRet
    ' Note that this log file will add Cr and Lf to the original string
    
    Dim Number As Integer

    'get next file number
    Number = FreeFile

    'Open file
    Open sPath For Input As Number

    'get entire file content
    sRet = Input(LOF(Number), Number)
    
    'Close File
    Close Number

    'transfers
    GetAllFileText = True

End Function