求助：VBA实现用户输入匹配对应工作表的功能完善

核心修改点 #

• 新增前缀识别逻辑：针对10和100两个前缀，弹出对应规则的补全输入框
• 完善输入校验：补全后仍检查工作表是否存在，非法后缀直接提示并重新引导输入
• 精简冗余代码：将数据集/图表的输入流程合并，避免重复逻辑
• 优化取消输入处理：用户取消后选择不退出时，自动重新执行流程

完整实现代码 #

Private Function SheetExists(name As String) As Boolean
    SheetExists = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = name Then
            SheetExists = True
            Exit Function
        End If
    Next
End Function

Private Function ConfirmEndSub() As VbMsgBoxResult
    ConfirmEndSub = MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion)
    If ConfirmEndSub = vbYes Then
        MsgBox "Thank You Goodbye"
    End If
End Function

Sub InputValidation()
    Dim userChoice As VbMsgBoxResult
    Dim inputText As String
    Dim suffixInput As String
    Dim fullSheetName As String
    Dim quitReply As VbMsgBoxResult
    
    ' 选择数据集或图表类型
    userChoice = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion + vbYesNo)
    
    ' 获取用户初始输入
    inputText = InputBox("Please enter a load value (10 or a load and trial (10-1))")
    
    ' 处理用户取消输入的分支
    If StrPtr(inputText) = 0 Then
        quitReply = ConfirmEndSub
        If quitReply = vbYes Then Exit Sub
        InputValidation ' 用户选择不退出，重新执行流程
        Exit Sub
    End If
    
    ' 情况1：输入完整工作表名称，直接激活
    If SheetExists(inputText) Then
        ThisWorkbook.Worksheets(inputText).Activate
        Exit Sub
    End If
    
    ' 情况2：输入前缀"10"，要求补全1/2
    If inputText = "10" Then
        suffixInput = InputBox("Please enter 1 or 2 to complete the sheet name (10-1/10-2)")
        If StrPtr(suffixInput) = 0 Then Exit Sub ' 取消补全则退出
        ' 校验后缀合法性
        If suffixInput <> "1" And suffixInput <> "2" Then
            MsgBox "Invalid input! Please enter 1 or 2."
            InputValidation
            Exit Sub
        End If
        fullSheetName = inputText & "-" & suffixInput
        If SheetExists(fullSheetName) Then
            ThisWorkbook.Worksheets(fullSheetName).Activate
        Else
            MsgBox "工作表不存在"
        End If
        Exit Sub
    End If
    
    ' 情况3：输入前缀"100"，要求补全1/2/3
    If inputText = "100" Then
        suffixInput = InputBox("Please enter 1, 2 or 3 to complete the sheet name (100-1/100-2/100-3)")
        If StrPtr(suffixInput) = 0 Then Exit Sub ' 取消补全则退出
        ' 校验后缀合法性
        If suffixInput <> "1" And suffixInput <> "2" And suffixInput <> "3" Then
            MsgBox "Invalid input! Please enter 1, 2 or 3."
            InputValidation
            Exit Sub
        End If
        fullSheetName = inputText & "-" & suffixInput
        If SheetExists(fullSheetName) Then
            ThisWorkbook.Worksheets(fullSheetName).Activate
        Else
            MsgBox "工作表不存在"
        End If
        Exit Sub
    End If
    
    ' 兜底：输入既不是完整表名也不是有效前缀
    MsgBox "工作表不存在"
End Sub

关键逻辑说明 #

1. 前缀补全规则：严格区分10和100的补全选项，避免用户输入错误后缀
2. 循环校验机制：非法输入后自动重新执行流程，无需用户手动触发
3. 安全退出处理：用户取消任意输入环节时，均提供退出确认选项
4. 工作表存在性检查：所有生成的表名都会经过SheetExists验证，确保不会触发运行时错误

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