Calling sub from CommandButton knowing which sheet is active
 

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?

Calling sub from CommandButton knowing which sheet is active
 

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
'<<=============


---
Regards,
Norman




"gtslabs" wrote in message
ps.com...
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?

Calling sub from CommandButton knowing which sheet is active
 

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.

Calling sub from CommandButton knowing which sheet is active
 

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.


---
Regards,
Norman