calling a sub function with a button in vba

0
votes

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 ?

2
vbaexcel
Attach a button to Sub calling()? What prevents you from dong that? – John Coleman
you mean , creating a button in a sheet and then assigning macro Sub calling to the button ? – Mikz
Yes, buttons can be placed directly in sheets and you already seem to know how to use buttons on userforms. There isn't all that much difference. – John Coleman
but i get an error expected function or variable – Mikz
So your sub calling has a bug? – John Coleman

2 Answers

0
votes

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

0
votes

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