How to reference "this sheet" in sheet code module?

Question

What's the "right" way to reference "this sheet" in a sheet code module (i.e. right-click View Code)?

For example, if "testit" is put into the Sheet1 code module, and Sheet2 is the active sheet when we run the "testit":

Sub testit()

MsgBox ActiveSheet.Name & _

    vbNewLine & Range("a1").Worksheet.Name

End Sub

The output is Sheet2 followed by Sheet1.  In other words, ActiveSheet is Sheet2 -- not surprisingly.

But I'm surprised that we must dereference Range("a1") -- any arbitrary cell -- in order to get to the sheet object that contains the code.

Is there a better way?

(I expected to find a widget called ThisSheet.)

BTW, I am interested in a (better) solution for "all" versions of Excel -- at least as far back as XL2003.  But if there is a solution for XL2007 and/or XL2010, I'm all ears.

Answer 1

In a worksheet module, the keyword Me refers to "this sheet".

In the ThisWorkbook module, the keyword Me refers to "this workbook".

So you can use

Sub TestIt()

    MsgBox Me.Name

End Sub

Answer 2

As a general rule of thumb, the code that goes in the module belonging to a worksheet is Eventcode that will automatically run when something occurs on the worksheet.

To find out what these events are, open a new workbook and right click a worksheet tab name and select View code. You will see 2 drop down boxes at the top of the white section of the VBA editor. The default values displayed in these will be (General) and (Declarations). Click the left drop down and select Worksheet and a default sub and end sub name will appear. Now click on the right dropdown and see all of the events that can be used. If you select one of the events then the sub and end sub will appear in the VBA editor and you can insert your code for the event.

If you add ActiveX controls to the worksheet then in the left dropdown you will see them listed also. If you select a control then a default sub and end sub will appear. The dropdown on the right will now display all of the events available for the ActiveX control and if you select them then the sub and end sub for the event will appear in the

VBA editor.

Note: You must leave the Sub names as they were inserted. If you change them then the event will not be called.

All other Subs and User Defined Functions (UDF's) are normally placed in a general or standard module. To insert a standard module, in the VBA editor select Insert -> Module. You can have more than one standard module and you can rename them the default of Module1, Module2 etc. (Select the module name on the left in the Project Explorer and press F4 to display the properties box.)

While most code can be written without changing the active worksheet and without selecting any ranges, if you do use code to change the Active worksheet when running event code and you then reference the active worksheet then you can get coding errors. If it is necessary to change the Active sheet then it is best to place most of the coding in a sub in a standard module and only use the Event code to call that sub in the standard module.

Hope this helps.

Answer 3

Hans wrote:

In a worksheet module, the keyword Me refers to "this sheet".

In the ThisWorkbook module, the keyword Me refers to "this workbook".

That's the one!  Thanks.  I forgot about me -- I mean Me :-).

Answer 4

Hi,

In a worksheet module the parent of any unqualified range object will  be the worksheet that contains the code. So

 Range("a1").Worksheet.Name

in a worksheet module is exactly the same as

Range("a1").Parent.Name

You said 'Is there are beter way' Yes there is never use unqualified range oblects

In the scenario you describe

ActiveSheet.Range("a1").Parent.Name

or

ActiveSheet.Range("a1").Worksheet.Name

returns Sheet2