Refer to the worksheet containing the current running code

[johnyeldham]

Hi

I think this should be simple, but I can't find a way to do this.

I am calling code from a Command Button which in turn calls code in a
Module. The code in the Module does some recurring copy and paste
operations to a chosen sheet. I need to pass the name of the sheet
where the command button resides to the sub in the Module. How do I do
this?

Currently, I have a private wrapper sitting in the button sheet. I
have tried guesses like Worksheet.Name - but that gives an error.
ActiveSheet might work - but I am not sure how to go about this.

The perfect solution would be to have the wrapper refer to the sheet
that the code (rather than the button) resides in, so I can call the
routine via other means than a button. In paraphrasing, object code
like the following would be perfect:

ThisPrivateSub.RelatedSheetObject.Name

Does anyone know how to do this?

Thanks for any help

John

 

[Dave Peterson]

You could pass the name of the worksheet or a reference to that worksheet
itself:

Behind the worksheet:

Option Explicit
Private Sub CommandButton1_Click()
Call testme01(Me)
'or
Call testme02(Me.Name)
End Sub

In a general module:

Option Explicit
Sub testme01(wks As Worksheet)
MsgBox wks.Name
End Sub
Sub testme02(wksName As String)
MsgBox wksName
End Sub


I like passing the worksheet reference. Then I get all the properties/methods
that can apply to worksheets.

 

[johnyeldham]

Thanks. The "me" object is what I am looking for.