Calling sub from CommandButton knowing which sheet is active

[gtslabs]

I have many sheets with a command button on it using the same code.
I want to change this so I have one code.

I put that code in a module and made it PUBLIC

Public Sub Process()
' do stuff
client = Me.Range("C4")
' do stuf
end sub


my Command button on each sheet now had this code:
Private Sub CommandButton2_Click()
Call process
end sub


The problem I have is at the "ME" statement gives me an error -
"Invalid use of ME keyword"
The subroutine takes inforrmation from the sheet with the command
button and puts it on a different sheet. How do I code so the sheet
the code is called from is the active sheet or it knows its the active
sheet?

 

[Norman Jones]

Hi G,

Try something like:

'=============>>
Private Sub CommandButton2_Click()
Call Process(Me)
End Sub

Public Sub Process(SH As Worksheet)
' do stuff
client = SH.Range("C4").Value
' do stuf
End Sub
'<<=============

 

[gtslabs]

Thanks Norman that worked.

How can I access that SUB from each of the 50 sheets I already have
made before I implemented this new code?

I tried copying the Command button to another sheet but it left the
code blank.
I also tried putting it in the Macro list to call it from each sheet
but the "Me" gave me and error also.

I remember vaugly that I if I select multiple sheets I can somehow
loop thru them all.

 

[Norman Jones]

Hi G,

'-------------------
How can I access that SUB from each of the 50 sheets I already have
made before I implemented this new code?

I tried copying the Command button to another sheet but it left the
code blank.
I also tried putting it in the Macro list to call it from each sheet
but the "Me" gave me and error also.

I remember vaugly that I if I select multiple sheets I can somehow
loop thru them all.
'-------------------

Rather than inserting a new CommandButton on 50+
sheets, why not insert a new button on a toolbar and
assign the required the requred macro to the toolbar
button. In this way, you could use the ActiveSheet
property to identify the sheet.