Excel VBA：未匹配指定工作表时如何弹出‘User Not Found’提示框？

可以通过两种方式实现需求：错误捕获判断或者遍历工作表检查，以下是具体实现：

方法一：错误捕获法（简洁高效） #

利用VBA的错误处理机制，尝试获取目标工作表，若获取失败则触发提示：

Private Sub CommandButton1_Click()
    Dim Path As String
    Dim Filename As String
    Dim targetWs As Worksheet ' 声明工作表变量

    Path = "C:\Users\OneDrive\Desktop\proj\"
    Filename = "Book1.xlsx"
    Workbooks.Open Filename:=Path & Filename

    ' 尝试获取目标工作表，捕获错误
    On Error Resume Next
    Set targetWs = Workbooks(Filename).Worksheets(TextBox1.Text)
    On Error GoTo 0 ' 恢复默认错误处理

    If Not targetWs Is Nothing Then
        ' 找到工作表，写入数据
        targetWs.Range("A1").Value = Me.TextBox1.Text
        targetWs.Range("B1").Value = Me.TextBox2.Text
        targetWs.Range("C3").Value = Me.TextBox3.Text
        targetWs.Range("D4").Value = Me.TextBox4.Text
        
        MsgBox "Submitted Successfully"
        ActiveWorkbook.Close SaveChanges:=True
    Else
        ' 未找到工作表，弹出提示
        MsgBox "User Not Found"
        ActiveWorkbook.Close SaveChanges:=False ' 无需保存，因为没修改
    End If
    
    Unload Me
End Sub

关键说明： #

• On Error Resume Next 让代码遇到错误时继续执行，不会直接崩溃
• Set targetWs = ... 尝试获取工作表，如果不存在，targetWs 会变成Nothing
• On Error GoTo 0 恢复默认的错误处理逻辑，避免后续代码忽略错误
• 找到工作表才保存修改，未找到则直接关闭不保存，避免无效操作

方法二：遍历工作表检查（直观无错误捕获） #

遍历目标工作簿的所有工作表，逐一比对名称：

Private Sub CommandButton1_Click()
    Dim Path As String
    Dim Filename As String
    Dim ws As Worksheet
    Dim wsFound As Boolean ' 标记是否找到工作表

    Path = "C:\Users\OneDrive\Desktop\proj\"
    Filename = "Book1.xlsx"
    Workbooks.Open Filename:=Path & Filename

    wsFound = False
    ' 遍历所有工作表查找匹配名称
    For Each ws In Workbooks(Filename).Worksheets
        If ws.Name = TextBox1.Text Then
            ' 写入数据
            ws.Range("A1").Value = Me.TextBox1.Text
            ws.Range("B1").Value = Me.TextBox2.Text
            ws.Range("C3").Value = Me.TextBox3.Text
            ws.Range("D4").Value = Me.TextBox4.Text
            
            wsFound = True
            Exit For ' 找到后退出循环
        End If
    Next ws

    If wsFound Then
        MsgBox "Submitted Successfully"
        ActiveWorkbook.Close SaveChanges:=True
    Else
        MsgBox "User Not Found"
        ActiveWorkbook.Close SaveChanges:=False
    End If
    
    Unload Me
End Sub

关键说明： #

• 用For Each遍历所有工作表，比对名称是否匹配
• 找到匹配项后立即退出循环，提升效率
• 用wsFound变量标记结果，后续根据标记执行不同逻辑

内容的提问来源于stack exchange，提问作者Kaypee Z adicman