Worksheet Event - Change

[RideTheLightning]

Hi all

On the Bet angel template spreadsheet, when it is connected, the timer changes each refresh in Cell C3, and I try to run the following Private sub in the "Bet Angel" Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then MsgBox "!!!!"
End Sub

Nothing happens. I would've expected it to trigger the message box as the timer is changing every second, but it doesn't. when I manually enter in data in cell C3, it triggers the message box.

I was wondering if anyone could explain why this occurs?

[Wyndon]

My VBA knowledge is sketchy and learned by cannibalising existing routines and trial and error. I seem to recollect a similar problem and the cause seemed to be that you couldn't operate a worksheet change event in the BetAngel sheet. If you do a link to the cell in question from another worksheet, you may find it will work. No guarantees and I'm prepared to be shot down in flames.

[nigelk]

Try brackets around the cell address.

If Target.Address = ("$C$3") Then

If you are just targeting a single cell, exclude all other possibilities first, so it would be:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> ("$C$3") Then Exit Sub

Range("G1") = "It Changed!"

End Sub

[RideTheLightning]

Tried both of those suggestions, Nigel, however getting the same result as before.

I basically want to avoid using the Worksheet Calculation Event trigger as I want a macro to do recalculation for a particular range of cells and not for the whole workbook.

[spreadbetting]

I think the change event is firing manually because you're changing the active cell when you hit enter, but simply using If Target.Address = "$C$3" Then MsgBox "!!!!" won't trigger a worksheet change.

[nigelk]

have you got any other code in the worksheet?

The attached works fine for me (BA Betdaq sheet)

[spreadbetting]

Your formula would only work for me manually not automatically, nigel.

Probably best if you say what you're actually trying to do, RideTheLightening, as it's probably only a case of exiting any Worksheet change if your criteria isn't met, you can use VBA to switch off automatic calcualtions and just target certain cells using something like

Range("A1:G20").Calculate

Or even copy data to different parts of the sheet if you want to preserve it.

[RideTheLightning]

Thanks Nigel, that works for me on the Betdaq Bet Angel, but not the Betfair one! That is surprising, why would that be?

I do have other code, but not on that Worksheet and it is a Worksheet Calculation and only works between certain times.

I will post a more detailed explanation of what I want to do later, spreadbetting. But thanks for your help in the meantime.

[RideTheLightning]

Just to clarify, I have a Private Sub Worksheet_Calculate in Sheet 2 of Bet Angel's excel template. It records Market data each refresh. Each new row is populated with the current refreshed Market data. The Problem is that I need the Calculation setting set to automatic to trigger the Worksheet Calculate event, but of course this leads to all open workbooks being re calculated. As spreadbetting suggested, I could do a calculation on a particular range, but that doesn't trigger the Worksheet calculate event. So I want to try and get the Worksheet_Change event working instead of the worksheet_calculate event.

[RideTheLightning]

Managed to get it sorted. Not entirely sure how I fixed it, could be a couple of reasons, haven't narrowed it down yet.

Anyway, thanks for your help guys.

[rinconpaul]

Managed to get it sorted. Not entirely sure how I fixed it, could be a couple of reasons, haven't narrowed it down yet.

Anyway, thanks for your help guys.

I'd be interested in how you solved this one? I'm having a similar issue with triggering macros based on event change. Specifically when a countdown timer reaches 00:00:00, an IF function changes a cell from "" to "YES", but nothing happens! Not until you hit Enter, F5 or a Trigger button.

[nigelk]

As no one else has come forward:

http://www.tradersandpunters.com/data-c ... readsheet/

You might also find the countdown timer on the Bet Angel sheet useful as well.

[RideTheLightning]

Managed to get it sorted. Not entirely sure how I fixed it, could be a couple of reasons, haven't narrowed it down yet.

Anyway, thanks for your help guys.

I'd be interested in how you solved this one? I'm having a similar issue with triggering macros based on event change. Specifically when a countdown timer reaches 00:00:00, an IF function changes a cell from "" to "YES", but nothing happens! Not until you hit Enter, F5 or a Trigger button.

Sorry, didn't notice a reply on this thread till today.

Do you have 'automatic Calculation' activated on the formula tab (to get the cell to change to "YES")?

You could put an If statement in your macro doing if countdown timer =00:00:00 then .....