如何從用戶輸入框中選擇作業表

我在編碼這個 VBA 代碼時遇到了問題。我需要代碼來詢問用戶是否需要資料或圖表。如果用戶從那里選擇是，我需要查看代碼以查看所選輸入并查看該輸入是否是有效的作業表名稱。如果沒有，輸入框將再次顯示，直到有效的作業表名稱。如果作業表有效，那么只要用戶輸入有效值，我就需要選擇或顯示作業表。我希望這是有道理的。

例如，如果用戶輸入 (10-1) 是有效作業表或 (1-1) 有效作業表，但如果是 (14-1) 或 (aa) 則不是有效作業表。

注意我還沒有進入繪圖部分，所以不要擔心用戶是否選擇 no。有人可以讓我朝著正確的方向前進嗎？

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

uj5u.com熱心網友回復：

請確保if在使用縮進時關閉所有陳述句？您現在設定它的方式意味著最后一個End if覆寫整個str = vbYes范圍，即如果str = vbNo什么也沒發生？

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

哪個也能解決問題？

隨后的陳述句嘗試選擇作業表，但這不起作用，因為您沒有在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

這完全沒有考慮到vbYesNo用戶總是會得到輸入框的初始答案？不確定這是否是意圖，但可惜它是這樣寫的。

另一個考慮因素是，如果用戶選擇不退出，則呼叫不會回傳輸入請求？=> 即它仍在結束例程...

標籤：擅长vbaexcel公式

上一篇：在Excel中復制到上面的行

下一篇：每列中最小值的最大值