VBA Excel to run same code to multiple commandbuttons

[12Rev79]

Hi Experts,

I have 10 Command Buttons in my Excel can anyone
help me the code that when I click CommandButton1 the value will be "Meal 1", CommandButton2 the value will be "Meal 2" so on and so forth...

Not necessarily clicking the buttons in sequence I could sometimes click CommandButton5 then 10, 3 or etc

I have tried the code below but I don't want to copy-paste the code to all of my button repeatedly.

is there other way to do it or a call function?

Private Sub CommandButton21_Click()
Range("M5").Select
Range(Selection, Selection).End(xlDown).Offset(0, 1).Select
Range(Selection, Selection).End(xlUp).Offset(1, 0).Value = "Meal 1"
Range(Selection, Selection).End(xlUp).Select

End Sub
____________________________----------------

Thanks in advance,
12Rev79

 

[Marc L]

Hi,​

​

differents ways like Application.Caller to see which button launches the procedure or​

just pass the number when calling the procedure at button level …​

​

 

[12Rev79]

I try to look for any related threads but still can not figure out the correct syntax,
I tried below but it shows me a message box "Error" only

Can you help me please the correct code?

Sub MyCallerButton()
Select Case TypeName(Application.Caller)
Case "Range" v = Application.Caller.Address
Case "String" v = Application.Caller
Case "Error" v = "Error"
Case Else v = "unknown"
End Select
MsgBox "caller = " & v
End Sub



Thanks in advance
12Rev79

 

[mikerickson]

If you have buttons from the Forms menu, you could asign each of them to this macro. Adjust their Names to the matching Meal.

Sub test()
    Select Case TypeName(Application.Caller)
        Case "Error"
            MsgBox "Called from VB Editor"
        Case "String"
        
            With ActiveSheet.Shapes(Application.Caller)
                Range(Selection, Selection).End(xlUp).Offset(1, 0).Value = Replace(.Name, "Button", "Meal")
            End With
        
        Case Else
            MsgBox TypeName(Application.Caller)
    End Select
End Sub

 

[12Rev79]

Thanks for your kind response mikericson however

I have the following buttons in my Worksheet4

I tried your code pasting in the module and Call in my command

Private Sub CommandButton21_Click()
Call test
End Sub

I got the message below

What I am trying to do is to have 1 code only for all my Meal buttons.
When I click Meal 1 for example it will give me a value in my Cells N6 = Meal 1
If Meal 4 next rows if is empty so on an so forth

Please help.

Thanks again.
12Rev79

 

[RoryA]

It appears you have activex buttons, not Form buttons. Change them to Form buttons and then you can use the code Mike posted.

 

[12Rev79]

If you have buttons from the Forms menu, you could asign each of them to this macro. Adjust their Names to the matching Meal.

Sub test()
    Select Case TypeName(Application.Caller)
        Case "Error"
            MsgBox "Called from VB Editor"
        Case "String"
       
            With ActiveSheet.Shapes(Application.Caller)
                Range(Selection, Selection).End(xlUp).Offset(1, 0).Value = Replace(.Name, "Button", "Meal")
            End With
       
        Case Else
            MsgBox TypeName(Application.Caller)
    End Select
End Sub

Thank you mikericson your kind help and patience with me are appreciated so much.

12Rev79