Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trigger worksheet activate event in another worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trigger worksheet activate event in another worksheet
On Jun 2, 7:11*am, Raj wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trigger worksheet activate event in another worksheet
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 '<<========= --- Regards. Norman "Raj" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Activate event if only one sheet in book | Excel Programming | |||
Msgbox in worksheet activate event if design mode is turned off | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Worksheet Activate Event | Excel Programming |