vba 检查特定工作表是否为活动工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7514868/
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
Check if a particular sheet is the activesheet
提问by Sangram Nandkhile
How to check if particular sheet is an active sheet or not?
如何检查特定工作表是否为活动工作表？
I want particular functionality to be used for worksheet having name Data.
我希望将特定功能用于名称为Data 的工作表。
I can check if Data sheet exists or not using following code
我可以使用以下代码检查数据表是否存在
Dim ws As Worksheet
Set ws = Wb.Sheets("Data")
If ws Is Nothing Then
Else
But how to check if Data sheet is an active sheet or not ? is there any thing like
但是如何检查数据表是否为活动表？有没有像
If ws Is Activesheet Then
UPDATE:
更新：
I have added following code in the one of the Class module of addin.
我在 addin 的 Class 模块之一中添加了以下代码。
What i am trying to do is to manage other excel sheets from this addin. I want to call procedure paste_cellsif the the active sheet is having name "Data".
我想要做的是从这个插件管理其他 excel 表。paste_cells如果活动工作表的名称为“数据”，我想调用过程。
Public WithEvents App As Application
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox "Activate"
Dim ws As Worksheet
Set ws = Wb.Sheets("Data")
If ws Is ActiveSheet Then ' if active sheet is having name Data
App.OnKey "^v", Procedure:="Paste_cell" 'paste cell is procedure i want to add when active sheet is Data
Else
App.OnKey "^v"
End If
End Sub
采纳答案by brettdj
you should
你应该
- use error handling as the sheet may not exist
For an addin you would normally use ActiveWorkbook,ie
Dim ws As Worksheet On Error Resume Next Set ws = ActiveWorkbook.Sheets("Data") On Error GoTo 0 If ws Is Nothing Then MsgBox "Data sheet not found" Else If ws.Name = ActiveWorkbook.ActiveSheet.Name Then MsgBox "Data sheet found and is active" Else MsgBox "Data sheet found but is inactive" End If End If
- 使用错误处理，因为工作表可能不存在
对于插件，您通常会使用 ActiveWorkbook，即
Dim ws As Worksheet On Error Resume Next Set ws = ActiveWorkbook.Sheets("Data") On Error GoTo 0 If ws Is Nothing Then MsgBox "Data sheet not found" Else If ws.Name = ActiveWorkbook.ActiveSheet.Name Then MsgBox "Data sheet found and is active" Else MsgBox "Data sheet found but is inactive" End If End If
回答by JMax
You can also check objects (we never know if the user has opened a workbook where the sheet has the same name):
您还可以检查对象（我们永远不知道用户是否打开了工作簿同名的工作簿）：
Sub test()
On Error Resume Next
If ActiveWorkbook.Worksheets("Data") Is ActiveSheet Then MsgBox ("ok")
On Error GoTo 0
End Sub
See MSDN
见MSDN
Thanks to brettdj for the reminder about the error handling.
感谢 brettdj 关于错误处理的提醒。
[EDIT] Within your code:
[编辑] 在您的代码中：
Public WithEvents App As Application
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox "Activate"
Dim ws As Worksheet
On Error Resume Next
Set ws = Wb.Sheets("Data")
On Error GoTo 0
If Not ws Is Nothing and ws Is ActiveSheet Then ' if active sheet is having name Data
App.OnKey "^v", Procedure:="Paste_cell" 'paste cell is procedure i want to add when active sheet is Data
Else
App.OnKey "^v"
End If
End Sub
回答by Jandrejc
I would use:
我会用：
If Wb.ActiveSheet.Name = ws.Name Then
End If
