Worksheet Change Event

**beat** · 01-21-2012, 05:15 AM

Hi,

I need to link a toggle button to a cell change but am not familiar with the worksheet change event which is what i think i need to use.

The cell to link to is a time in the format 00:00:00 and each time it decreases + and the toggle button is true i want the code to run.

Sub togglebutton1_Change(ByVal Target as Range)

**JapanDave** · 01-21-2012, 07:00 AM

Can you post a dummy workbook? And also include what you want to achieve?

**Bmoe** · 01-21-2012, 09:17 AM

I agree with JapanDave a sample workbook would be best. However the only thing to help with your question is for your (byVal Target as Range) line there. Target is a variable that refers to a range object. So you could do anything to Target that you can do to a range. Range("B1").value can be Target.Value. Target.Row, Target.Interior... and so on.

**beat** · 01-22-2012, 04:33 AM

Ok let me explain further.

I use a trading application that has excel integration and refreshes the worksheet every 100ms.
I have toggle buttons that link ranges together based on true/false criteria.
At the moment i have everything working under worksheet_calculate but the problem is the sheet refreshing to quickly on certain rules.
Hence why i want to link certain aspects to a countdown timer or other slower methods which is where i think i need to use the worksheet change/changeevent.

Here is an example sheet which at the moment is using the worksheet_calculate.

I want to link the scanning of these if statements to the countdown timer in "B4" instead of refreshing with everything else each 100ms.

Hope that makes more sense,

Beat

**beat** · 01-23-2012, 04:18 AM

Was a Sunday yesterday so i'm assuming this got a bit lost down the list so please dont frown upon this bump.

**beat** · 01-23-2012, 12:36 PM

**Bmoe** · 01-23-2012, 01:02 PM

Worksheet change I don't think fires until you change the cell. An update from Excel won't do it, but I could be wrong.

But I'm still a bit confused. You are refresing your sheet 10x a second is that causing a calculation event (thats pretty intense)? I would probably use a MOD(format(NOW(), "unit of choice"), interval of choice) to only do something when the minute is divisible by 15 or something then flag the interval to only run once. It looks for 15/30/45 and only runs it one time, then on the hour it sets it back?? Something like that perhaps?

**Bmoe** · 01-23-2012, 01:10 PM

Worksheet change I don't think fires until you change the cell. An update from Excel won't do it, but I could be wrong.

But I'm still a bit confused. You are refresing your sheet 10x a second is that causing a calculation event (thats pretty intense)? I would probably use a MOD(format(NOW(), "unit of choice"), interval of choice) to only do something when the minute is divisible by 15 or something then flag the interval to only run once. It looks for 15/30/45 and only runs it one time, then on the hour it sets it back?? Something like that perhaps?

**beat** · 01-23-2012, 02:06 PM

All i want to do is have excel look at a set of rules each time a certain cell changes value.

Ive probably made things look more complicated than they are.

Example

Cell D10 is a Clock that refreshes automatically every second.

When it refreshes/changes value then do XYZ