Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Knowing the creation date of an excel sheet email attachment | Excel Discussion (Misc queries) | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
How save active workbook w/o knowing name | Excel Programming | |||
Referencing an External Sheet without Knowing its Name - XL2K | Excel Programming | |||
Need an EXPERT in VBA and knowing how to manipulate objects on an excel sheet | Excel Programming |