Excel vba: call Worksheet_change from another worksheet

Question

Suppose I have a workbook with two worksheets:

Sheet1 and Sheet2.

There is a Worksheet_change sub in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    ...
End Sub

There is a Worksheet_Calculate in Sheet2:

Private Sub Worksheet_Calculate()
    'Call Sheets("Sheet1").Worksheet_Change(Range("A1"))  'doesn't work
End Sub

How do I call the Sheet1'a Worksheet_Change subroutine from Worksheet_Calcalculate in Sheet2?

Answer 1

You can use Application.Run like that …

Application.Run "Sheet1.Worksheet_Change", Range("A1")

Note that Sheet1 here is the VBA name of the sheet not the name on the tab. The VBA name can be changed in the property window of the sheet (in the VB Editor).

---

Alternatively move the code in your Worksheet_Change into a module like:

Public Sub MySpecificWorksheet_Change(ByVal Target As Range)
    ...
End Sub

And call that from both Worksheet_Change and Worksheet_Calculate

Private Sub Worksheet_Change(ByVal Target As Range)
    MySpecificWorksheet_Change(ByVal Target As Range)
End Sub

Private Sub Worksheet_Calculate()
    MySpecificWorksheet_Change(ByVal Target As Range)
End Sub

Answer 2

Either:

• Change Private Sub Worksheet_Change to Friend Sub Worksheet_Change.
• Move the functionality of Private Sub Worksheet_Change into a friend/public sub and call it from both Worksheet_Change and Worksheet_Calculate. [Recommended]