开发者

How can I get a reference to the worksheet an event was generated in?

In a worksheet I can attach code to an event like so:

Private Sub Worksheet_Calculate()
  ' .. code here
End Sub

How can I get a reference to the worksheet the event was generated in?

I wan开发者_开发技巧t to have a 100% secure way, so I don't have to worry about code breaking when the name worksheet changes etc.

ActiveSheet is not correct because it is not guaranteed that any sheet is active upon (re)calculate.


You can use caller:

Application.Caller.Worksheet.Name


You can use the Codename property:

Private Sub Worksheet_Calculate()
Debug.Print Me.CodeName
End Sub

By default it's the same name as the worksheet, but you can change it in the Properties Window of the VBE - it's the Name property. The user can't change it. (Copying a sheet adds a number to the name, e.g., Sheet1 becomes Sheet11.

You can also use it in the workbook-level event:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.CodeName = "TheSheetICareAbout" Then
Debug.Print Sh.Name
End If
End Sub

It's hard to be more helpful as you don't say how you'll use the reference.


Some more information on code names:

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜