Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refer to current row in formula | Excel Worksheet Functions | |||
how to prevent code running when in a worksheet code | Excel Programming | |||
how to I refer to current workbook without using its name? | Excel Worksheet Functions | |||
Refer to Worksheet Ranges in Code | Excel Programming | |||
More then 1 Excel object running at same time , how can I refer to right one in code? | Excel Programming |