CODEWITHSUNDEEP
excelvba
user3614882
user3614882

Reputation: 195

VBA call a SUB in a Sheet from module

I am trying to call a Sub I defined inside a Sheet, from a Module.

The code I use in my Module (which happens to be inside a Sub):

CallByName Worksheets("Sheet_name"), "Sub_name", vbMethod

But Excel does not execute the Sub. The Module and Sheet are inside the same workbook.

Upvotes: 10

Views: 49268

Answers (4)

Mike Syracuse
Mike Syracuse

Reputation: 71

for me, I had to change the Sheet Sub Procedure from Private to Public for the Call to work.

Call Worksheets("Sheet_name").Sub_name

In my case:

Call Worksheets("Sheet 20").CommandButton1_Click

Upvotes: 7

Mohammad ElNesr
Mohammad ElNesr

Reputation: 2667

From any module or sheet (that has a code), if you have a sub called Button1_click() in "Sheet12" and you want to call it from other module or sheet. just type

call Sheets("Sheet12").Button1_click 'Without parenthesis

Upvotes: 2

Estevam Garcia
Estevam Garcia

Reputation: 445

You need to set your function to Public as shown below

Sheet1:

Public Sub test()
    MsgBox "Got here!"
End Sub

Module:

Sub callTest()
    With Sheet1
        Call .test
    End With
End Sub

Upvotes: 8

SierraOscar
SierraOscar

Reputation: 17637

If it's in the same project and of Public scope - just use the name of the sub.

If it's in a another workbook, use Application.Run()

1.

Sub Foo()
    Bar '// Runs Sub Bar()
End Sub

Sub Bar()
    MsgBox "I got called!"
End Sub

2. 

Sub Foo()
    Application.Run "MyOtherWorkbook.xlsm!Bar"
End Sub

Upvotes: 3

Related Questions