Call single macro from multiple from control buttons

[soylentone]

Hi - I have multiple buttons on a sheet. I would like to call a single Sub from these buttons and pass a paramater depending on which button was pressed. How can I do this?

Thanks!

 

[HalfAce]

Hello soylentone, welcome to the board.
This shouldn't be hard to do. What are the different parameters you mentioned?
(And you may want to post the (relevant part of the) code too.)

 

[soylentone]

The buttons run a timer on a sheet. These buttons are repeted on multiple sheets.

These buttons are named "Timer 60", "Timer 90" and "Timer 120". Right now when the user clicks "Timer 60" it runs a macro which runs a count down timer on the sheet. And so on when the user clicks "Timer 90" ...

Problem is I have 3 different macros which all have the same code bar the time integer... UGLY.

I could just have each of the macros attached to the Form Buttons call another macro and pass a paramater but this would still be a bit UGLY. I'd rather just detect which button was pressed and proceed in a single sub if possible by just setting the time integer.

Sorry not a big VBAer - I'm a C rat ;;;

Thanks.

 

[soylentone]

HI - I've done it like this for now which is not too bad. I think I remember years back seeing VBA code which would allow us to detect which button was pressed in a single macro and just use a simple case structure but who knows... Too may languages running jumbled in my head... Thanks for the help.

Sub RunTimer(timeInSeconds As Integer)
    StoreActiveCell
    
    Range("J5").Select
    ActiveCell.FormulaR1C1 = timeInSeconds
    Call Timer
    
    RestoreActiveCell
End Sub
Sub FortyFiveSecondsTimer()
    RunTimer (45)
End Sub
Sub SixtySecondsTimer()
    RunTimer (60)
End Sub
Sub NintySecondsTimer()
    RunTimer (90)
End Sub
Sub OneTwentySecondsTimer()
    RunTimer (120)
End Sub

 

[Smitty]

Welcome to the Board!

You can use Application.Caller:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> WhichButton()<br>     <br>     MsgBox "You pressed Button " & Application.Caller & _<br>    " - " & ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text<br>     <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[soylentone]

Smitty - You the man! Thanks everyone.

Sub RunTimer()
    StoreActiveCell
 
    Range("J5").Select
 
    'http://www.mrexcel.com/forum/showthread.php?p=2345954&posted=1#post2345954
 
    'This pulls the first element from the splitting of the text on the pressed button.
    'Split(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text, " ")(0)
    'So if a button with text of "60 Second Timer" is pressed "60" is captured.
 
    ActiveCell.FormulaR1C1 = _
        Split(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text, " ")(0)
 
    Call Timer
 
    RestoreActiveCell
 
End Sub

 

[soylentone]

I grabbed the Timer code from here also. Here is the whole module. Assign any form control - who text has the first word as a number - to the "RunTimer" sub and the time will tick down in cell 'J5'.

Public activeRow As Variant
Public activeCol As Variant
 
'http::/www.mrexcel.com/archive/VBA/2357.html
Dim CountDown As Date
 
'http::/www.mrexcel.com/archive/VBA/2357.html
Private Sub Timer()
    CountDown = Now + TimeValue("00:00:01")
    Application.OnTime CountDown, "Reset"
End Sub
 
'http::/www.mrexcel.com/archive/VBA/2357.html
Private Sub Reset()
    Dim count As Range
    Set count = [J5] ' J5 contains the number of seconds for the countdown.
    count.Value = count.Value - 1
    If count <= 0 Then
    MsgBox "Countdown complete."
    Exit Sub
    End If
    Call Timer
End Sub
 
'http::/www.mrexcel.com/archive/VBA/2357.html
Private Sub DisableTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub
 
Private Sub StoreActiveCell()
    activeRow = ActiveCell.row
    activeCol = ActiveCell.Column
End Sub
 
Private Sub RestoreActiveCell()
    Cells(activeRow, activeCol).Select
End Sub
 
Public Sub RunTimer()
    StoreActiveCell
 
    Range("J5").Select
 
    'http://www.mrexcel.com/forum/showthread.php?p=2345954&posted=1#post2345954
 
    'This pulls the first element from the splitting of the text on the pressed button.
    'Split(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text, " ")(0)
    'So if a button with text of "60 Second Timer" is pressed "60" is captured.
 
    ActiveCell.FormulaR1C1 = _
        Split(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text, " ")(0)
 
    Call Timer
    RestoreActiveCell
 
End Sub