Command buttons and code - help!!
 

Hi everyone,

OK, so i have a Worksheet with five command buttons on, i.e. RunASH_Mon,
RunASH_Tues etc etc

I call the following code on each of the buttons (there is actually 10
worksheets all with five buttons with the same names), and this works fine
but I want to be able to pass the command button name, as I have with the
worksheet name, so that I only have one sub routine instead of five.


Sub RunASHMon(SheetN As String)

Application.ScreenUpdating = False

Sheets("Data").Activate
ActiveSheet.Range("A1:D119").Copy
Sheets(SheetN).Activate
ActiveSheet.Range("A6").Select
ActiveSheet.Paste

Application.ScreenUpdating = True

End Sub

Any ideas how I would do this, maybe with as IF....THEN statement? Basically
I want it to say,

IF (the command button name is RunASH_Mon and has been clicked) THEN
ActiveSheet.Range ("A6").Select.Paste
IF (RunASH_Tues has been clicked) THEN
ActiveSheet.Range("G6").Select.Paste
etc etc

Any help would be greatly appreciated

Cheers
Lindsey M

Command buttons and code - help!!
 

Lindsey,

If you are using buttons from the Forms toolbar
then something like this...

'--------------------------------------------------
Sub ButtonTest()
Dim strName As String
strName = Application.Caller

Select Case strName
Case "Button 1"
ActiveSheet.Range("A6").Select
Case "Button 2"
ActiveSheet.Range("B10").Paste
Case "Button 3"
ActiveSheet.Range("C15").Paste
Case "Button 4"
ActiveSheet.Range("D20").Paste
Case "Button 5"
ActiveSheet.Range("E25").Paste
End Select

End Sub
'-------------------------------------------------

Regards,
Jim Cone
San Francisco, USA



"Lindsey M" wrote in message
...
Hi everyone,

OK, so i have a Worksheet with five command buttons on, i.e. RunASH_Mon,
RunASH_Tues etc etc

I call the following code on each of the buttons (there is actually 10
worksheets all with five buttons with the same names), and this works fine
but I want to be able to pass the command button name, as I have with the
worksheet name, so that I only have one sub routine instead of five.
Sub RunASHMon(SheetN As String)
Application.ScreenUpdating = False
Sheets("Data").Activate
ActiveSheet.Range("A1:D119").Copy
Sheets(SheetN).Activate
ActiveSheet.Range("A6").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Sub

Any ideas how I would do this, maybe with as IF....THEN statement? Basically
I want it to say,

IF (the command button name is RunASH_Mon and has been clicked) THEN
ActiveSheet.Range ("A6").Select.Paste
IF (RunASH_Tues has been clicked) THEN
ActiveSheet.Range("G6").Select.Paste
etc etc
Any help would be greatly appreciated
Cheers
Lindsey M