excel - InputBox 的工作表选择问题

Question

我有一个带有以下代码的 Excel VBA 文件。我的问题是 InputBox不能正常工作。有10床单。第一张表称为“菜单”。其他工作表如工作表 2 - 10。应用工作表 3,4 和 5 VeryHidden .请帮我纠正它。

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet

    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> "Menu" Then
            Sheet3.Visible = xlSheetVeryHidden
            Sheet4.Visible = xlSheetVeryHidden
            Sheet5.Visible = xlSheetVeryHidden
        End If
    Next Sh

    Dim myList As String
    Dim i As Integer
    Dim mySht

     i = 1
     For Each oSheet In ActiveWorkbook.Sheets
         If oSheet.Visible <> xlSheetVeryHidden Then

            myList = myList & i & " - " & oSheet.Name & " " & vbCr
            i = i + 1
         End If
    Next oSheet

    mySht = InputBox("Select Sheet to go to." & vbCr & myList)
    ActiveWorkbook.Sheets(CInt(mySht)).Select
End Sub

Answer 1

就像我在上面的评论中所说的那样；问题是Sheets(CInt(mySht)) .
问题
当你指定一个数字时，说 3 ，然后代码 Sheets(CInt(mySht))变成 Sheets(3) .但这不是你想要的。当您将该数字与 " - " 连接时，您需要该数字后面的名称然后使用工作表名称。 Sheets(3)实际上可能指的是隐藏表而不是第三个可见表，因此您会收到错误消息。
选项

而不是使用 myList ，使用数组。

在用户做出选择后拆分数组，然后转到该表

解决方案
这是你正在尝试的吗？

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet
    Dim ShName As String
    Dim i As Integer
    Dim mySht, MyAr
    
    For Each Sh In ThisWorkbook.Worksheets
        Sh.Visible = xlSheetVisible
    Next Sh
    
    Sheet3.Visible = xlSheetVeryHidden
    Sheet4.Visible = xlSheetVeryHidden
    Sheet5.Visible = xlSheetVeryHidden
    
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Visible = xlSheetVeryHidden Then i = i + 1
    Next Sh

    ReDim MyAr(1 To ThisWorkbook.Sheets.Count - i)
    
    i = 1
    
    '~~> Store the names of all visible sheets in the array
    For Each Sh In ActiveWorkbook.Sheets
        If Sh.Visible = xlSheetVisible Then
            MyAr(i) = i & " - " & Sh.Name
            i = i + 1
        End If
    Next Sh
    
    '~~> Get user input
    mySht = InputBox("Select Sheet to go to." & vbCr & Join(MyAr, vbNewLine))
    
    If IsNumeric(mySht) Then
        '~~> Get the actual sheet name using split as
        '~~> we had actually appended " - " to it earlier
        ShName = Trim(Split(MyAr(mySht), " - ")(1))
        '~~> Activate the sheet
        ThisWorkbook.Sheets(ShName).Activate
    End If
End Sub