Refer to the worksheet containing the current running code
 

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

Refer to the worksheet containing the current running code
 

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.




" wrote:

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

Refer to the worksheet containing the current running code
 

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

Dave Peterson wrote:
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.




" wrote:

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