excel 如何从用户输入框中选择工作表

我在编写此VBA代码时遇到问题。我需要此代码来询问用户是需要数据还是图形。如果用户在此处选择“是”，我需要此代码来查看选定的输入，并查看该输入是否为有效的工作表名称。如果不是，输入框将再次显示，直到显示有效的工作表名称。如果工作表有效，那么我需要在用户输入有效值时选择或显示工作表。
例如，如果使用者输入（10-1）为有效工作表，或输入（1-1）为有效工作表，但如果输入（14-1）或（a-a）则为无效工作表。
注意我还没有到绘图部分，所以不要担心用户是否选择了否。有人能给我正确的方向吗？

Sub InputValidation()
    Dim str As String
    Dim inp As String
    Dim ws As Worksheet

    
    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion + vbYesNo)
    
    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
            Exit Sub
            End If
        ElseIf inp = "#-#" Or "##-#" Then
                If Sheets(ws).Name = inp Then
                    Worksheets(inp).Activate
                End If
        Else
            MsgBox "This load and test cannot be found"
    
    If str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
            Exit Sub
            End If
        End If
    End If
        
End Sub

请确保在使用缩进时关闭所有if语句？您现在设置的方式意味着最后一个End if覆盖了整个str = vbYes作用域，即如果str = vbNo没有任何React？

Sub InputValidation()
    Dim str As String
    Dim inp As String
    Dim ws As Worksheet

    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion + vbYesNo)

    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
                Exit Sub
            'End If
        ElseIf inp = "#-#" Or "##-#" Then
            If Sheets(ws).Name = inp Then
                Worksheets(inp).Activate
            End If
        Else
            MsgBox "This load and test cannot be found"
        End If '=> added this End If as it will otherwise skip to end of sequence
    ElseIf str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
                Exit Sub
            End If
        End If

End Sub

我也认为你下面的一行不正确？

ElseIf inp = "#-#" Or "##-#" Then

它将给予一个Type Mismatch错误？（错误13）您可以考虑使用正则表达式进行此评估或简单地使用类似下面的东西？

ElseIf inp like "*-*" Then

哪一个也能达到目的？
随后的语句试图选择Worksheet，但由于您没有在任何地方设置ws对象，因此无法执行该操作。因此，下面的语句在许多方面都是不正确的：

If Sheets(ws).Name = inp Then

请看下面的代码，它将给予你一个很好的基础，从？这也将摆脱不必要的inp = "#-#"比较

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
    Next
    
End Function

Private Function ConfirmEndSub()
    
    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 str As String
    Dim inp As String
    Dim ws As Worksheet

    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion + vbYesNo)

    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            Reply = ConfirmEndSub
            If Reply = vbYes Then Exit Sub
        ElseIf SheetExists(inp) Then
            Worksheets(inp).Activate
        Else
            MsgBox "This load and test cannot be found"
        End If
    ElseIf str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            Reply = ConfirmEndSub
            If Reply = vbYes Then Exit Sub
        End If
    End If
End Sub

这完全不考虑depsite的初始vbYesNo答案，用户将总是得到输入框？不确定这是否是意图，但唉，这就是它是如何写的。
另一个注意事项是，如果用户选择不退出，则调用不会返回到输入请求？=〉即，它仍在结束例程...

excel 如何从用户输入框中选择工作表

1条答案

相关问题

热门标签

最新问答