求反向代码：UserForm初始化时将工作表值回填至表单文本框

Got it, let's work through this to get your UserForm auto-populating with saved worksheet data when it loads. Here's a straightforward, robust solution tailored to your needs:

First, let's align on the setup I'm assuming (you can adjust to your exact sheet/control names):

• Your worksheet (let's call it DataSheet) has names in Column A, with corresponding data in Columns B, C, D, etc.
• Your UserForm has a ComboBox1 (loaded with Column A names) and TextBox1, TextBox2, etc. for the associated data.

Step 1: Update the UserForm_Initialize Event #

This is where we'll load the name list and pull saved data into the TextBoxes. Paste this code into your UserForm's code module:

Private Sub UserForm_Initialize()
    Dim dataSheet As Worksheet
    Dim lastRow As Long
    Dim targetName As String
    Dim matchRow As Variant
    
    ' Set reference to your data worksheet (update the sheet name!)
    Set dataSheet = ThisWorkbook.Worksheets("DataSheet")
    
    ' 1. Load ComboBox with names from Column A (keep your original functionality)
    lastRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row
    ComboBox1.Clear
    ' Skip header row if you have one (change A2 to A1 if no header)
    ComboBox1.List = dataSheet.Range("A2:A" & lastRow).Value
    
    ' 2. Choose which name's data to load (pick one option below)
    ' Option A: Load the last saved name (store this when submitting data)
    targetName = dataSheet.Range("Z1").Value ' Use a hidden column/cell to save the last name
    ' Option B: Load the first name in the list as default
    ' If ComboBox1.ListCount > 0 Then targetName = ComboBox1.List(0)
    
    ' 3. Find the row matching the target name
    If targetName <> "" Then
        matchRow = Application.Match(targetName, dataSheet.Range("A:A"), 0)
        If Not IsError(matchRow) Then
            ' 4. Populate TextBoxes with values from the matched row
            TextBox1.Value = dataSheet.Cells(matchRow, "B").Value ' Map to your data columns
            TextBox2.Value = dataSheet.Cells(matchRow, "C").Value
            TextBox3.Value = dataSheet.Cells(matchRow, "D").Value
            
            ' Set ComboBox to the loaded name for consistency
            ComboBox1.Value = targetName
        End If
    End If
End Sub

Step 2: Update Your Submit Button Code (to Save the Last Edited Name) #

To make sure the form loads the last edited entry next time, add a line to your submit button code to save the selected name:

Private Sub CommandButton_Submit_Click()
    Dim dataSheet As Worksheet
    Dim matchRow As Variant
    
    Set dataSheet = ThisWorkbook.Worksheets("DataSheet")
    
    ' Find the row for the selected name
    matchRow = Application.Match(ComboBox1.Value, dataSheet.Range("A:A"), 0)
    If Not IsError(matchRow) Then
        ' Write TextBox values to the worksheet (keep your original logic)
        dataSheet.Cells(matchRow, "B").Value = TextBox1.Value
        dataSheet.Cells(matchRow, "C").Value = TextBox2.Value
        dataSheet.Cells(matchRow, "D").Value = TextBox3.Value
        
        ' Save the current selected name for next form load
        dataSheet.Range("Z1").Value = ComboBox1.Value
        
        MsgBox "Data saved!", vbInformation
    Else
        MsgBox "Name not found in the list!", vbExclamation
    End If
End Sub

Key Notes to Customize for Your Setup #

• Worksheet Name: Replace "DataSheet" with your actual worksheet name.
• Column Mapping: Adjust the column letters (e.g., "B", "C") to match where your data is stored in the worksheet.
• Target Name Logic: If you don't want to load the last edited name, swap to Option B (load first name) or even add a prompt to select a name on load.
• Error Handling: The code uses IsError to avoid crashes if the target name isn't found in Column A.

内容的提问来源于stack exchange，提问作者user3806255