trigger worksheet activate event in another worksheet

[Raj]

Hi,

I have some code written in the worksheet activate event of
Worksheet1. This creates a pivot table in Worksheet2 and writes some
other data to Worksheet1 based on this pivot table. I want the same
thing to happen when Worksheet2 is activated ie the code in Worksheet
activate event Iin Worksheet 1 should run, create the pivot table in
Worksheet2 and then write the data in Worksheet 1.

Any ideas on how to do this? Specifically, can I use Worksheet2's
activate event to activate Worksheet 1 whose code should then get
triggered? Will it result in an infinite loop. In which case, how do I
break the loop.

Thanks in advance for the help.

Regards,
Raj

 

[JW]

Hi,

I have some code written in the worksheet activate event of
Worksheet1. This creates a pivot table in Worksheet2 and writes some
other data  to Worksheet1 based on this pivot table. I want the same
thing to happen when Worksheet2 is activated  ie the code in Worksheet
activate event Iin Worksheet 1 should run, create the pivot table in
Worksheet2 and then write the data in Worksheet 1.

Any ideas on how to do this? Specifically, can I use Worksheet2's
activate event to activate Worksheet 1 whose code should then get
triggered? Will it result in an infinite loop. In which case, how do I
break the loop.

Thanks in advance for the help.

Regards,
Raj

Why not just place the code in a regular module and then call that
code from each active event?

Regular module
Sub YourCode()
'Your Code Here
End Sub

Then, in each of the worksheet modules, place this
Private Sub Worksheet_Activate()
YourCode
End Sub

 

[Norman Jones]

Hi Raj,

Two possible solutions present themselves:

(1)

In Sheet2's code module. try::

'=========>>
Option Explicit

Private Sub Worksheet_Activate()
Application.Run "Sheet1.Worksheet_Activate"
End Sub
'<<=========


(2) An alternative solution would be to move the processing code into a
procedure housed in a standard module; the procedure would then be called
from the activate events in each of the two sheets.

Thus, schematically:

Standard module

'=========>>
Option Explicit

Public Sub mySub()

'\\ Your pivot and processsing code

End Sub
'<<=========

In Sheet1:

'=========>>
Option Explicit

Private Sub Worksheet_Activate()
Call mySub
End Sub
'<<=========

In Sheet2:

'=========>>
Option Explicit

Private Sub Worksheet_Activate()
Call mySub
End Sub
'<<=========

If processing code requires knowledge of activated sheet, you can pass this
information as a parameter, i.e:

Standard module

'=========>>
Option Explicit

Public Sub mySub(sSheetName as string)
Dim SH As WorkSheet

Set SH = ThisWorkbook,Sheets(sSheetName)
'\\ Your pivot and processsing code

End Sub
'<<=========

In Sheet1:

'=========>>
Option Explicit

Private Sub Worksheet_Activate()
Call mySub (Me.Name)
End Sub
'<<=========

In Sheet2:

'=========>>
Option Explicit

Private Sub Worksheet_Activate()
Call mySub (Me.Name)
End Sub
'<<=========