VBA用户窗体代码问题：按指定工作表名打开时Case Else异常

Hey there! Let's sort out that Case Else issue and get your code working exactly how you want it. The core problem with relying on Case Else in a Select Case block here is that it only handles the specific sheet names you list—any valid sheet name you don't include will trigger the error message, even if it exists. Instead, a more robust approach is to first check if the sheet actually exists in the workbook, then act accordingly.

Here's the Corrected Full Code #

Private Sub CommandButton1_Click()
    Dim sheetName As String
    Dim targetSheet As Worksheet
    
    ' Get user input for the sheet name
    sheetName = InputBox("In which sheet do you wish to enter data? Specify sheet name as Toner, Copy Paper,etc.", "Input")
    
    ' Handle the case where user clicks Cancel (empty input)
    If sheetName = "" Then
        MsgBox "No sheet name entered. Please try again.", vbExclamation
        Exit Sub
    End If
    
    ' Attempt to reference the target worksheet (suppress errors temporarily)
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0 ' Reset error handling to default
    
    ' Check if the worksheet exists
    If Not targetSheet Is Nothing Then
        ' Activate the sheet if it's found
        targetSheet.Activate
        MsgBox "Successfully opened sheet: " & sheetName, vbInformation
    Else
        ' Show the required error message if sheet doesn't exist
        MsgBox "Please use another Sheet name", vbExclamation
    End If
End Sub

If You Must Use Select Case (For Restricted Sheet Names) #

If you only want users to pick from a specific list of sheet names (like "Toner" or "Copy Paper"), you can fix your original Case Else block like this:

Private Sub CommandButton1_Click()
    Dim whichSheet As String
    
    whichSheet = InputBox("In which sheet do you wish to enter data? Specify sheet number as Toner, Copy Paper,etc.", "Input")
    
    Select Case whichSheet
        Case "Toner"
            ThisWorkbook.Worksheets("Toner").Activate
        Case "Copy Paper"
            ThisWorkbook.Worksheets("Copy Paper").Activate
        ' Add other allowed sheet names here
        Case Else
            ' Properly trigger the error message for invalid entries
            MsgBox "Please use another Sheet name", vbExclamation
            Exit Sub
    End Select
End Sub

Just note this method will reject any sheet name not explicitly listed—even if it's a valid sheet in the workbook.

Key Explanations #

• Error Handling for Sheet Existence: The first code uses On Error Resume Next to safely check if the sheet exists without crashing. If the sheet doesn't exist, targetSheet stays as Nothing, which we then check to show the error message.
• Cancel Input Handling: We added a check for empty input (when the user clicks Cancel) to avoid unnecessary errors.
• Robustness: The first approach works for any valid sheet name in the workbook, making it more flexible than a hardcoded Select Case list.

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