應用程式 VBA/關閉時備份文字框

概述

此 VBA 程式碼是為 Microsoft Excel 編寫的，但可以輕鬆地適應 MS Office 套件中的其他應用程式。它在每次關閉窗體時將使用者窗體文字框中的所有文字儲存到日誌檔案中。然後，稍後在重新開啟窗體時，或在任何其他時間，使用者可以使用最近儲存的文字填充文字框。

VBA 程式碼

該程式碼需要一個名為 Userform1 的使用者窗體，兩個文字框，TextBox1 和 TextBox2，以及一個名為 CommandButton1 的命令按鈕。將 UserForm1 屬性 ShowModal 設定為 false 以方便學習。將下面的程式碼複製到三個相應的模組中，並使用 xlsm 檔案字尾儲存工作簿。
文字框中找到的任何程式碼都將在使用者窗體關閉時儲存。這包括使用者窗體的無意關閉或工作簿的故意關閉。當然，它不保護使用者免受電源故障的影響。資料的儲存無需人工干預，因此如果要避免敏感資料的儲存，則需要考慮這一點。
日誌檔名為 SavedText.txt，它將位於與工作簿相同的資料夾中。如果未找到該名稱的日誌檔案，則程式碼會建立它以供使用。日誌檔案只有兩個欄位，文字框名稱和其中找到的字串內容。逗號分隔符被避免，因為它不太可能遇到字串 >Break<。
儲存功能從 UserForm_QueryClose 事件執行。SaveTextBoxes() 在使用者窗體控制元件迴圈中建立日誌字串，然後透過 WriteToFile() 匯出字串。
WriteToFile() 會在不存在日誌檔案時建立日誌檔案，但否則會覆蓋找到的任何文字，因此只有最近儲存的會話會保留在那裡。在其他地方使用日誌記錄過程的使用者應注意，在記錄條目末尾額外儲存了 Cr 和 Lf，可能需要考慮。
RestoreTextBoxes() 僅透過按 CommandButton1 執行，因此使用者可以選擇是否插入文字。GetAllFileText() 一次匯入所有日誌檔案的內容，檔案保留內容直到下次被覆蓋。字串被拆分兩次，一次是將其分成行，即每個文字框記錄一行，然後再次將每個記錄分成兩個欄位以匹配主傳輸迴圈中的控制元件名稱。

程式碼變更

2019 年 3 月 8 日：將資料分隔符從逗號更改為其他符號，在標準模組中

對於 ThisWorkbook 模組

'...............................................
' 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

對於 Userform1 模組

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

對於標準模組

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

參見

外部連結