Excel vba: call Worksheet_change from another worksheet

user_8052882 0
Viewed 7088

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?

vba excel
3 Answers

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
user_32196130

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]
user_116830

Calling the events in a worksheet is not different than calling any non-explicitly "Private" Sub or Function in any module or object. If they are not Public or Friend, they do not give their Functions, Subs and Properties being accessed without mentioning the place where they stay.

If the events are non-modified with the key word Private before them, they could be called with this code:

Sub TestMe
    Sheet1.Worksheet_Change Range("A1")
End Sub

If someone has explicitly added the word Private to start of the event, so it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range), then we should really consider not accessing the event. If still, we want to access it, it can be done with Application.Run.

Application.Run "Sheet1.Worksheet_Change", Sheet1.Range("A1")
Application.Run CStr(Worksheets(1).CodeName & ".Worksheet_Change"), Range("A1")
user_54486260
Related