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 See Also External Links