Conditionally run VBA code

Question

Is it possible to have multipe buttons, lets say 'Button 1' and 'Button 2' run the same VBA code but yield a different result based on the button that was pressed?

For instance when I press button 1 I want it to go to a website, load data and put it on to Sheet 1. But when I press button 2, it goes to the same site and loads it to Sheet 2.

I know I can have multiple instances of the same VBA code (with different names) however I am hoping to simplify the code and prevent it from being overly complicated.

Related [How to call a macro from a button and pass arguments](http://stackoverflow.com/questions/29858176/how-to-call-a-macro-from-a-button-and-pass-arguments) and then just pass an argument to test with an if statement or other control statement — LinkBerest, Feb 02 '16 at 04:46

score 5 · Answer 1 · answered Feb 02 '16 at 04:47

5

If you are using a Forms button you can assign the same macro and use Application.Caller to return the name/id of the calling button.

Sub Test()
MsgBox Application.Caller & " was pressed"
End Sub

answered Feb 02 '16 at 04:47

brettdj

54,857
16
114
177

Scott Holtzman · Accepted Answer · 2016-02-02T05:39:32.767

4

Create one sub to do the work and pass the sheetname as an argument to that sub. I did it with a string variable, but you can do it with a worksheet variable as well.

   Sub Button1_Click()

      LoadWebsiteToSheet "Sheet1"

   End Sub

   Sub Button2_Click()

      LoadWebsiteToSheet "Sheet2"

   End Sub

   Sub LoadWebsiteToSheet(sName as String) 

        '... code to load website to Worksheets(sName)

   End Sub

edited Feb 02 '16 at 05:39

answered Feb 02 '16 at 04:46

Scott Holtzman

27,099
5
37
72

I get an error message indicating "Sub or function not defined" I copied and pasted the code as you provided, altered the naming, namely Button1/Button2 to Weel1/Week2. – user1664305 Feb 02 '16 at 05:30
@user1664305 See my edits. I forgot part of the name in the last part. also, add your code where I have the comment. – Scott Holtzman Feb 02 '16 at 05:40
Sorry, I didn't see that edit! Tried it out and it works like a charm! Thank you for your help! – user1664305 Feb 02 '16 at 05:45
@user1664305 - i just did the edit based on your comment! :) Glad it works :) – Scott Holtzman Feb 02 '16 at 05:47

score 0 · Answer 3 · answered Feb 02 '16 at 08:12

I was leaning more towards brettdj's solution as well

If you assign the same macro to many buttons, you will get different results based on the button name.

You could name the button to the sheet you wanted. Practice with this. Add several buttons and assign this code to them. Click each button to see what happens.

Sub GetButtonName()
    Dim Btn As String

    Btn = ActiveSheet.Shapes(Application.Caller).Name
    MsgBox Btn

End Sub

Conditionally run VBA code

3 Answers3