基于InputBox的纸张选择问题
基于InputBox的纸张选择问题
提问于 2014-11-28 06:44:11
我有一个具有以下代码的Excel文件。我的问题是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回答 1
Stack Overflow用户
回答已采纳
发布于 2014-11-28 07:12:27
正如我在上面的评论中所说的，问题是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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27183311
复制相关文章
相似问题
腾讯云开发者
