Worksheet_Change vs. Worksheet_Calculate

[London_Calling]

It’s been a while since I’ve done this and I’m completely confused about what to put where.

I know the event module (for the relevant sheet) needs to be set to Worksheet_Change, but that means it won’t calculate formulas. So, that suggests formulas need to go in sheet modules and be referenced from the event module.

If I am I on the right road with that, can anyone think what else I might bear in mind?

I'm very confused . . .

Thanks

[osknows]

Both can be placed in the Sheet object.

Private Sub Worksheet_Change (ByVal Target As Range) only fires if a cell on the sheet physically changes. It won't fire the event for recalculations only, eg if you're trying to monitor the change in a formula this won't work unless the formula is caused by a changed cell on the same sheet.

Private Sub Worksheet_Calculate() fires the event after any formulae on the sheet updates. This is useful for occasions where you have a sheet which contains only formulae fed from other sheets/workbooks and you need to monitor the changes in the formulae

Does that makes things clearer?

[London_Calling]

osknows, It certainly helps - thanks.

I'm getting so frustrated because nothing on the sheet is changing without me effecting a change by Ctrl+S (i.e. saving the sheet). I can' even get something ultra simple like putting "= A5" in, say, Z5 to work without first saving.

Could you help me work through a simple example? If, say, [E2] changes from "Not In Play" to "In Play", [E2] has (as you say in relation to Worksheet_Change) physically changed. Could you walk me through the mental and coding steps to have another cell on the sheet change when that Not/In PLay happens?

By the way, I'm very grateful for your interest.

[Ian]

You need something like this ....

Sub Worksheet_Change(ByVal Target As Range)

If LCase(Cells(2, 5).Value) <> PrevStatus Then

PrevStatus = LCase(Cells(2, 5).Value)

Application.EnableEvents = False
Application.Calculation = xlManual

If LCase(Cells(2, 5).Value) = "In Play"

>>>>>>>> Whatever you want to do

End If


Application.Calculation = xlAutomatic
Application.EnableEvents = True

End If
End If

End Sub

[osknows]

It sounds like you have auto calculation turned off in excel (Goto Tools/Options/Calculation and set calculation to automatic)

Let me know if that hasn't improved things with the formula not updating before saving.

With reference to code it sounds like you should be using the worksheet_change event, this is an example of logging the time an event went into play, hopefully you can work out what's happening

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

    'log time race went in play
      If Cells(2, 5).Value = "In Play" And Cells(3, 17).Value = "" Then
       Cells(3, 17).Value = Cells(2, 4).Value
       ElseIf Cells(2, 5).Value <> "In Play" Then
       Cells(3, 17).Value = ""
       End If
       
Application.EnableEvents = True
End If

End Sub

'If Target.Columns.Count = 16 Then' is specific to Betting Assistant and checks that BA has written to excel by counting the columns written on each refresh. If you remove this line the code underneath will run for ALL changes to the worksheet and not just when BA makes the change.

'Application.EnableEvents = False' turns off events to prevent any looping, if you write to the sheet it retriggers the change event and so on... which you don't want.

Also, check out the Excel section of the Help files as there are some example spreadsheets with code you can examine

[Ian]

I think "In Play" should be "in play" in my example.

[London_Calling]

It sounds like you have auto calculation turned off in excel (Goto Tools/Options/Calculation and set calculation to automatic)

Thank goodness! I thought I was going mad! That's got the sheet moving at last, but as you later suggest, it's not the whole problem . . .


To both Ian and osknows, I've got the most dreadful head ache and I probably need to get some air for a while. Very, very grateful for your help. If you could spare a little time to pop back either later or tomorrow, there will be unresolved Change/Calculate issues . . .

Phew for the sheet anyway!

Thanks again to both.