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
?
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
Either:
Private Sub Worksheet_Change
to Friend Sub Worksheet_Change
.Private Sub Worksheet_Change
into a friend/public sub and call it from both Worksheet_Change
and Worksheet_Calculate
. [Recommended]If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With