Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("$h$1")

[mrwhitney]

HI all, sorry if this has been asked before but in practice mode a change H1 changing to Suspended isn't registering - is there something I'm missing? thanks

[spreadbetting]

Think you'd need to post your code up as there could be many reasons it's not registering from simple things like typos to just having a different sheet in focus.

[mrwhitney]

Hi, thanks, it's this routine for football markets, which is supposed to launch a macro (countticks) when H1 triggers to say the market is suspended, changing the value of cell b71 from False to True:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$b$71").Address Then
If Range("$B$71") = True Then
Call countticks
End If
End If
End Sub


It works when I type suspended into h1, but not when I run the sheet in practice mode.

[jimibt]

tbh, i haven't touched the Excel templates for a while, but I do recall that the target range isn't always a single cell and you therefore need to use the Intersect method. This is from an old project that I was running a while back that needed to do the same thing. Relevent code only included:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSource As Worksheet
Dim targetRange  As Range

    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    Set targetRange = wsSource.Range("$B$71")

    If Not Application.Intersect(targetRange, wsSource.Range(Target.Address)) Is Nothing Then
        ' do you logic here as before
        ' i.e. you've ensured that your targetRange is part of the change event
        If targetRange  = True Then
            Call countTicks
        End If
    End If

End Sub

[spreadbetting]

Hi, thanks, it's this routine for football markets, which is supposed to launch a macro (countticks) when H1 triggers to say the market is suspended, changing the value of cell b71 from False to True:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$b$71").Address Then
If Range("$B$71") = True Then
Call countticks
End If
End If
End Sub


It works when I type suspended into h1, but not when I run the sheet in practice mode.

Worksheet_Change doesn't trigger from formulas, you'd have to use worksheet_calculate or even simply omit the target address line

[mrwhitney]

Thanks for pointing me in the right direction Jimibt and Spreadbetting,

I'm going to have another go at this next week, running out of time now. It's still working when I'm no connected to BA so something's throwing it off. There's a video showing what can be done with this here https://www.youtube.com/watch?v=TYyPrqqFgVg and it'll be a very useful thing to have once up and running, I just need to get my head around it.

[spreadbetting]

Yep worksheet events can get confusing and there are plenty of them. Thing to remember is the Change event will fire when something on the sheet is physically changed , so for example you enter something into a cell or BA writes data to cells. The Worksheet_Calculate fires when some calculation has happened and that can be as simple =A1+A2 in a cell or =IF(A1="suspended",TRUE,FALSE). But a worksheet_Change won't fire because a formula has recalculated as the formula is still the same in the cell.

They'll both still work if you're not connected to BA as they're checking for changes/calulations on the excel sheet and not dependant on BA.

[Derek27]

I've got some code in the worksheet change event that changes data in the worksheet and causes the event to fire again. It doesn't appear to be a problem as the nested executions of the code get ignored.

Should I just ignore it or should I set a switch to exit the sub, flicking it off when I've finished my code, or is there any other way of stopping the event repeatedly firing?

[spreadbetting]

I've got some code in the worksheet change event that changes data in the worksheet and causes the event to fire again. It doesn't appear to be a problem as the nested executions of the code get ignored.

Should I just ignore it or should I set a switch to exit the sub, flicking it off when I've finished my code, or is there any other way of stopping the event repeatedly firing?

You can turn off events so any changes within the sheet due to your code won't fire off another event. You simply turn them back on at the end of the code.

Code: Select all

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

''''''Your code in here

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[spreadbetting]

One thing to remember is if you have errors within the code , say whilst testing, it may not get to the section of code that re-enables events so worth either having a simple macro to reset events manually

Code: Select all

Sub reset()
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End sub

Or considering adding some kind of error trapping to your code

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo xit:


''''''Your code in here



Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Derek27]

Cheers SB.