vba 激活 Excel 2010 工作簿中的隐藏工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18130576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Activating hidden sheet within workbook Excel 2010
提问by Mr_Thomas
I have a summary sheet called 'Summary' which has a link on it. This link goes to the following sub:
我有一个名为“Summary”的汇总表，上面有一个链接。此链接转到以下子项：
Sub WTButton()
Call GoToWorksheet("Wilmington")
End Sub
As you can see, this calls another sub called GoToWorksheet. It looks like this:
如您所见，这调用了另一个名为GoToWorksheet. 它看起来像这样：
Sub GoToWorksheet(strWorksheet As String)
On Error GoTo Err_GoToWorksheet
With Worksheets("Summary")
If Worksheets(strWorksheet).Visible = xlSheetHidden Then
Worksheets(strWorksheet).Visible = xlSheetVisible
End If
Worksheets(strWorksheet).Activate
End With
Exit_GoToWorksheet:
Exit Sub
Err_GoToWorksheet:
MsgBox Err.Description, vbCritical, "GoToWorksheet Error"
Resume Exit_GoToWorksheet
End Sub
I want the sheet to remain hidden until the end user is ready to add data to it. This whole system works great IF the sheet is already visible. If the sheet is hidden, it does not make it visible and it does not activate it.
我希望工作表保持隐藏状态，直到最终用户准备好向其中添加数据。如果工作表已经可见，则整个系统运行良好。如果工作表处于隐藏状态，则不会使其可见，也不会激活它。
What am I doing wrong?
我究竟做错了什么？
LINK TO FILE: https://www.dropbox.com/s/wsv9al410m7kwda/Book1Test.xlsm
文件链接：https: //www.dropbox.com/s/wsv9al410m7kwda/Book1Test.xlsm
采纳答案by Mr_Thomas
Here's what I did to make things work: I got rid of the GoToWorksheetsubroutine. I then made the WTButtonsub look like this:
这是我为使事情正常所做的工作：我摆脱了GoToWorksheet子例程。然后我让WTButton子看起来像这样：
Sub WTButton()
Worksheets("Wilmington").Visible = xlSheetVisible
Worksheets("Wilmington").Activate
Range("A3").Select
End Sub
This makes it much more direct [Note: I took over this project from someone else]
这使它更直接[注意：我从别人那里接手了这个项目]
回答by Patrick Honorez
Not tested but should work...
未测试但应该可以工作...
Sub GoToWorksheet(strWorksheet As String)
On Error GoTo Err_GoToWorksheet
With Worksheets(strWorksheet)
If .Visible = xlSheetHidden Then
.Visible = xlSheetVisible
End If
.Activate
End With
Exit_GoToWorksheet:
Exit Sub
Err_GoToWorksheet:
MsgBox Err.Description, vbCritical, "GoToWorksheet Error"
Resume Exit_GoToWorksheet
End Sub
回答by SeanC
The Withis not needed, and it does not test for xlVeryHidden
该With是不需要的，并且它不测试xlVeryHidden
I would write the routine this way:
我会这样写程序：
Option Explicit
'This makes sure I find spelling errors
Sub GoToWorksheet(strWorksheet As String)
On Error GoTo Err_GoToWorksheet 'Should only fail if worksheet name is wrong
Worksheets(strWorksheet).Visible = xlSheetVisible
'just make it visible - don't care what state it was in previously
Worksheets(strWorksheet).Activate
Exit Sub
Err_GoToWorksheet:
MsgBox Err.Description, vbCritical, "GoToWorksheet Error"
End Sub
and for testing, to discover where exactly the error is, I would comment out all the error handling
为了测试，为了发现错误的确切位置，我会注释掉所有的错误处理
