excel - 编辑 VBA 脚本用户窗体

Question

Private Sub CommandButton1_Click()
Dim Sheetname As String
whichSheet = InputBox("In which sheet do you wish to enter data? Type in sheet name as Toner, Copy Paper, Alteration, Mail Package, Gloves, and Special Requests.", "Input")
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.CodeName
        Case "Toner", "Copy Paper", "Mail Package", "Alteration", "Gloves", "Special Requests"
            Worksheets(whichSheet).Activate ws.Name
        Case Else
            If ws.CodeName <> ("Toner")("Copy Paper"), "Mail Package", "Alteration", "Gloves", "Special Requests") Then
            MsgBox "Please use another Sheet name"
            Exit Sub
            End If
    End Select
Next ws

大家好，我正在尝试让此代码使用确切的工作表名称作为输入来打开 Excel 中的特定工作表。如果工作表名称不存在或拼写不正确，则应显示“请使用其他工作表名称”消息。到目前为止我已经有了这个，但它甚至无法识别正确的工作表名称。感谢您的帮助。

Answer 1

如果我理解正确，我认为你过于复杂化了，这就足够了。您只需要检查通过输入框输入的工作表名称，如果它属于第一个列表，则激活它(我假设)。

Private Sub CommandButton1_Click()

Dim whichSheet As String
whichSheet = InputBox("In which sheet do you wish to enter data? Type in sheet name as Toner, Copy Paper, Alteration, Mail Package, Gloves, and Special Requests.", "Input")

Select Case whichSheet
    Case "Toner", "Copy Paper", "Mail Package", "Alteration", "Gloves", "Special Requests"
        Worksheets(whichSheet).Activate
    Case Else
        MsgBox "Please use another Sheet name"
        Exit Sub
End Select

End Sub