calling a sub function with a button in vba

Question

I have 10 Sub function macros in "This workbook". Instead of running each macros, everytime i wanted an userform with button, and when I click , it should execute all the Sub function in "This workbook".

I created another function like calling, and I used the following code,

Sub calling()


Call lookup
Call RangeTest
 Call datecompare
Call AutoPivot
Call Autochart
 Call pivot
Call chart
Call pivot1
End Sub

so, whenever I go for macros, I select Sub calling() and it executes the 10 Sub functions. I would like to have it through a button click. Could anyone suggest how I can do this ?

Answer 1

You should simply add a button from the developper ribbon and assign it to calling(). However, make sure all your macros are in the same module or else you'll have to specify it. If you still get an error it would be interesting to see on what line the error is coming from.

Thank you

Answer 2

I'm assuming the confusion arises because you are trying to use an ActiveX button. If you were using a Form Button, the macro name would appear in the "Assign Macro" popup box. But the ActiveX version doesn't provide the dialog, and because the button exists on the page, doesn't easily see the macros from "This Workbook".

Here's what worked for me.

Within Sheet3:

Private Sub CommandButton1_Click()
    Application.Run ("'ThisWorkbook.calling'")
End Sub