Multiple Worksheets 3

[osx99]

The following code works for 1 worksheet. If I had multiple worksheets that are all updating independently from a 3rd party app. What's the correct syntax to fire multiple events and ensure the range updates on the required worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)

Range("a1").Value = -5

End Sub

Does this event placed in each sheet object only select the range("a1") in which the event has fired?

Thanks,
Os

 

[SkipVought]

Hi,

The code you posted will fire INDENINITELY on this sheet: the FIRST time it will run because some value on that sheet changes and the SUBSEQUENT TIMES because the value in A1 changed when the code ran the PREVIOUS TIME.

So it would be better if...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Range("a1").Value = -5

Application.EnableEvents = True

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

Sorry I know that and just removed the enableevents code for simplicity

My question is does the Range("a1").Value = -5 set a1 in the sheet from which the event was fired?

Eg Sheet(1) is visible on my screen but the 3rd party app may update cells in Sheet(3) without me visibly seeing it. Do i need to prefix Range("a1").Value = -5 with anything to ensure it updates SHeet(3)?

 

[SkipVought]

My question is does the Range("a1").Value = -5 set a1 in the sheet from which the event was fired?

A change occurs on sheet1.

Sheet1 change event fires.

If a change occurs on sheet3, the sheet1 change event will NOT fire.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Use a Workbook event and Sheets(3).Range

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osx99]

I've been thinking about this and perhaps I need to ask more fundamental questions.....

What happens if multiple worksheets update simultaneously and I place a worksheet_change event in each? And what happens if a worksheet updates again before the event in another worksheet either runs or has completed?

Does vba/excel run the event any order and does order of changes get retained in realtime?

 

[osx99]

I'll try and elaborate a little more on what I'm trying to do

I have a 3rd party app which writes to 1 workbook with 5 seperate excel sheets at different intervals

so in realtime sheet(1) may be written to at t = 0ms, t=200, t= 400; Sheet(2) t= 5ms, t = 505, t= 1005 etc

These times are not constant and may change

I need to run some code to update each sheet only whent hat sheet changes but the app doesn't activate or select any worksheets or cells so I don't know at any point in time which sheet is updating as the order may change due to different refresh rates in each worksheet

I hoped to use multiple worksheet_change events in each sheet object in the vba editor to only update that sheet once a change occurs.

As no sheet is activated I need to ensure the range("a1").value updates in the correct sheet

It has just occured to me that because each sheet refreshes at different times could it stop an event firing as another triggers either during or before another event?

 

[SkipVought]

1. I would consider each sheet as a table. I would not have multiple processes updating the same sheet.

2. I would aggregate or report the data from the tables on a separate sheet. Hopefully there is a data/time stamp on the tables. I would periodically perform this aggregation using a criteria that would return the subset of data of interest. I would NOT use events in the table sheets.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

That's brought some real clarity to how best to achieve what I need! Thank you once again!
Os

 

[DaveInIowa]

Could you move your logic over to the Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) event?