如何基于Excel命名范围ID（A列）的值选择行填充用户窗体？

我想根据A列（已定义命名范围为"ID"）中的ID值选择对应行，重新填充用户窗体。当前代码可以运行，但它是按行号来选择行，不是按A列的ID值。我猜应该把ID声明为Range类型，但不知道后续该怎么操作？

原代码：

Private Sub CommandButton3_Click()
'selectID, repopulate userform, and alter existing booking,

Dim MyRow As Variant
   
    MyRow = UserForm2.TextBox22.value
    
      If Not IsNumeric(MyRow) Then
        MsgBox "Please enter an ID number in TextBox22", vbExclamation, "Invalid row entry."
      
    ElseIf Application.WorksheetFunction.CountA(Rows(Val(MyRow)).Range("A1:C1")) = 0 Then
        MsgBox "Row " & Val(MyRow) & " is empty. Please enter a valid ID number.", vbExclamation, "Invalid ID column row number"
    
    Else
     Sheet5.Activate
        UserForm2.TextBox21.value = Range("A" & Val(MyRow)).value
        UserForm2.TextBox1.value = Range("b" & Val(MyRow)).value
        UserForm2.TextBox2.value = Range("C" & Val(MyRow)).value
        UserForm2.TextBox3.value = Range("D" & Val(MyRow)).value
        UserForm2.TextBox4.value = Range("E" & Val(MyRow)).value
        UserForm2.TextBox5.value = Range("F" & Val(MyRow)).value
        UserForm2.TextBox6.value = Range("G" & Val(MyRow)).value
        UserForm2.TextBox7.value = Range("H" & Val(MyRow)).value
        UserForm2.TextBox8.value = Range("I" & Val(MyRow)).value
        UserForm2.TextBox9.value = Range("J" & Val(MyRow)).value
        UserForm2.TextBox10.value = Range("K" & Val(MyRow)).value
        UserForm2.TextBox11.value = Range("L" & Val(MyRow)).value
        UserForm2.TextBox12.value = Range("M" & Val(MyRow)).value
        UserForm2.TextBox13.value = Range("N" & Val(MyRow)).value
        UserForm2.TextBox14.value = Range("O" & Val(MyRow)).value
        UserForm2.TextBox15.value = Range("P" & Val(MyRow)).value
        UserForm2.TextBox16.value = Range("R" & Val(MyRow)).value
        UserForm2.TextBox17.value = Range("S" & Val(MyRow)).value
    End If
    
        TextBox20.Text = DateDiff("d", TextBox6.value, TextBox7.value)
                
    If Not IsNumeric(TextBox11) Then
        MsgBox "Please enter total charge in TextBox11", vbExclamation, "Invalid row entry."
    End If
        
      Result = MsgBox("Are these entries all correct?", vbYesNo + vbQuestion)
        If Result = vbYes Then
        
            CommandButton1_Click  'to refill calendar entry
            
        Else: MsgBox "Alter info then press Confirm"
    End If
 
End Sub

要实现按ID值查找对应行的功能，你可以使用Range.Find方法在命名范围"ID"中搜索输入的ID值，找到后获取该行的行号，再用这个行号填充窗体控件。以下是修改后的代码：

Private Sub CommandButton3_Click()
    ' 根据ID选择行、重新填充用户窗体并修改现有预订
    Dim inputID As Variant
    Dim idRange As Range
    Dim foundCell As Range
    Dim targetRow As Long
    
    inputID = UserForm2.TextBox22.Value
    
    ' 验证输入是否为数字
    If Not IsNumeric(inputID) Then
        MsgBox "请在TextBox22中输入有效的ID编号", vbExclamation, "无效输入"
        Exit Sub
    End If
    
    ' 引用命名范围"ID"
    Set idRange = ThisWorkbook.Names("ID").RefersToRange
    
    ' 在ID列中查找输入的ID值（精确匹配）
    Set foundCell = idRange.Find(What:=Val(inputID), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    ' 处理未找到ID的情况
    If foundCell Is Nothing Then
        MsgBox "未找到ID为 " & Val(inputID) & " 的记录，请输入有效的ID编号", vbExclamation, "ID不存在"
        Exit Sub
    End If
    
    targetRow = foundCell.Row
    
    ' 验证目标行是否为空（检查A-C列）
    If Application.WorksheetFunction.CountA(Sheet5.Rows(targetRow).Range("A1:C1")) = 0 Then
        MsgBox "ID为 " & Val(inputID) & " 的记录行是空的，请重新确认", vbExclamation, "无效记录"
        Exit Sub
    End If
    
    ' 填充用户窗体控件
    With UserForm2
        .TextBox21.Value = Sheet5.Range("A" & targetRow).Value
        .TextBox1.Value = Sheet5.Range("B" & targetRow).Value
        .TextBox2.Value = Sheet5.Range("C" & targetRow).Value
        .TextBox3.Value = Sheet5.Range("D" & targetRow).Value
        .TextBox4.Value = Sheet5.Range("E" & targetRow).Value
        .TextBox5.Value = Sheet5.Range("F" & targetRow).Value
        .TextBox6.Value = Sheet5.Range("G" & targetRow).Value
        .TextBox7.Value = Sheet5.Range("H" & targetRow).Value
        .TextBox8.Value = Sheet5.Range("I" & targetRow).Value
        .TextBox9.Value = Sheet5.Range("J" & targetRow).Value
        .TextBox10.Value = Sheet5.Range("K" & targetRow).Value
        .TextBox11.Value = Sheet5.Range("L" & targetRow).Value
        .TextBox12.Value = Sheet5.Range("M" & targetRow).Value
        .TextBox13.Value = Sheet5.Range("N" & targetRow).Value
        .TextBox14.Value = Sheet5.Range("O" & targetRow).Value
        .TextBox15.Value = Sheet5.Range("P" & targetRow).Value
        .TextBox16.Value = Sheet5.Range("R" & targetRow).Value
        .TextBox17.Value = Sheet5.Range("S" & targetRow).Value
        
        ' 计算日期差
        .TextBox20.Text = DateDiff("d", .TextBox6.Value, .TextBox7.Value)
    End With
    
    ' 验证TextBox11是否为数字
    If Not IsNumeric(UserForm2.TextBox11.Value) Then
        MsgBox "请在TextBox11中输入有效的总费用", vbExclamation, "无效输入"
        Exit Sub
    End If
    
    ' 确认信息是否正确
    If MsgBox("所有输入信息是否正确？", vbYesNo + vbQuestion) = vbYes Then
        CommandButton1_Click ' 重新填充日历条目
    Else
        MsgBox "修改信息后请点击确认按钮"
    End If
End Sub

修改要点说明 #

• 直接引用命名范围：通过ThisWorkbook.Names("ID").RefersToRange获取A列的ID范围，避免硬编码列位置，适配范围的动态调整。
• 精确查找ID：用Range.Find的LookAt:=xlWhole参数确保完全匹配输入的ID值，避免部分匹配导致的错误。
• 增强错误处理：新增未找到ID的提示，避免无效行操作；同时保留了原有的空行验证逻辑。
• 简化代码结构：使用With UserForm2减少重复代码，提升可读性；移除Sheet5.Activate，直接通过工作表引用范围，代码更高效稳定。

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