vba 如何仅在打开工作簿后第一次激活特定工作表时运行宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24382123/
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
How to run a macro only for the first time a particular sheet is activated after opening the workbook?
提问by Syed Kaleel Awn
I want to run a macro when a particular sheet is selected. But the trick is I want to run it only the first time that sheet is selected after opening the workbook.
我想在选择特定工作表时运行宏。但诀窍是我只想在打开工作簿后第一次选择该工作表时运行它。
回答by rex
Sorry I originally misread your question.
抱歉，我最初误读了您的问题。
Put this code in the worksheet code:
将此代码放在工作表代码中：
Public sheetOpenned As Boolean ' should go at the top of the page
Private Sub Worksheet_Activate()
If sheetOpenned = False Then
' run your macro
sheetOpenned = True
End If
End Sub
Note though that this sub will not run when the workbook is opened and the sheet is the default one that's active. I suspect you would have to add another piece of code to handle that scenario in the Workbook_Open()event.
请注意，当工作簿打开并且工作表是默认的活动工作表时，此子不会运行。我怀疑您必须添加另一段代码来处理Workbook_Open()事件中的这种情况。
回答by Tony Dallimore
Look up Events within Excel VB Editor Help. There are lots of events and you can write routines to be called when one of interest occurs.
在 Excel VB 编辑器帮助中查找事件。有很多事件，您可以编写例程以在感兴趣的事件发生时调用。
The routines below do not handle the user creating new worksheets or changing the sequence of existing ones but should give you some ideas for how to achieve the effect you seek.
下面的例程不处理用户创建新工作表或更改现有工作表的顺序，但应该为您提供一些关于如何实现您寻求的效果的想法。
Open a workbook and then the VB Editor. The project explorer is down the left hand side. Expand Microsoft Excel Objectsif necessary by clicking the plus sign against it. Click ThisWorkbookand an empty code area will be displayed. Copy the code below in that code area. Save and close the workbook. Reopen the workbook and switch between sheets. Open the VB Editor to view the Debug.Print output. Close the workbook to see the final display.
打开工作簿，然后打开 VB 编辑器。项目浏览器位于左侧。Microsoft Excel Objects如有必要，通过单击它旁边的加号来展开。单击ThisWorkbook，将显示一个空代码区域。在该代码区域复制下面的代码。保存并关闭工作簿。重新打开工作簿并在工作表之间切换。打开 VB 编辑器以查看 Debug.Print 输出。关闭工作簿以查看最终显示。
Hope this helps.
希望这可以帮助。
Option Explicit
Dim ActivateCount() As Long
Sub Workbook_Open()
Dim InxS As Long
Debug.Print "Workbook """ & ActiveWorkbook.Name & """ opened"
ReDim ActivateCount(1 To Sheets.Count)
' Entries will be initialised to zero.
' SheetActivate is not called for the initial sheet
For InxS = 1 To Sheets.Count
If Sheets(InxS).Name = ActiveSheet.Name Then
ActivateCount(InxS) = ActivateCount(InxS) + 1
Debug.Print "Worksheet """ & ActiveSheet.Name & """ activation count = " & ActivateCount(InxS)
Exit For
End If
Next
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim InxS As Long
For InxS = 1 To Sheets.Count
If Sheets(InxS).Name = Sh.Name Then
ActivateCount(InxS) = ActivateCount(InxS) + 1
Exit For
End If
Next
Debug.Print "Worksheet """ & Sh.Name & """ activation count = " & ActivateCount(InxS)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim InxS As Long
Dim Text As String
Text = "Activation counts"
For InxS = 1 To Sheets.Count
Text = Text & vbLf & " " & ActivateCount(InxS) & " " & Sheets(InxS).Name
Next
Call MsgBox(Text, vbOKOnly)
End Sub
