Help on Worksheet_calculate

[keith.wheeldon]

Hi all

I have created a sheet to record odds data which places a bet when certain conditions are met. I am recording several races 2hours before start time on multiple sheets through guardian

The problem is when I connect and let it run, the code below only seems to work on the selected sheet. Is this how worksheet_calculate works or am I missing something else?

Private worksheet_calculate()

If range("zy3").value= True Then Call PlaceBet()

End sub

Any help really appreciated, thanks.

[Tanden]

worksheet_calculate should work regardless of which sheet is active.

The issue may be in your PlaceBet().

It depends on where PlaceBet() is and how it refers to any cells it changes.

If it is on every worksheet then any changes it makes should happen on the worksheet it belongs to - if they don't then make sure the code isn't explicitly referencing a particular sheet. Eg The code within worksheet2 may be referencing worksheet1 by mistake.

If you have put PlaceBet() in a user module then any changes it makes will only happen on the active worksheet.

To solve this you can provide the worksheet as a parameter so PlaceBet could start like...

Public Sub PlaceBet(ws as worksheet)

And when you call it from within a worksheet you could use something like...

If range("zy3").value= True Then Call PlaceBet(Me)

Then, whenever you update cells within PlaceBet make sure your code uses the worksheet variable ws

Eg if you previously referenced Range("Z1") then use ws.Range("Z1")

[keith.wheeldon]

I have indeed got PlaceBet() in a module, so that is probably where my issue lies. I will try using the parameter in tomorrow's races and hopefully it will work. Thanks for your help Tanden. I will let you know how I get on tomorrow.

[spreadbetting]

The problem is when I connect and let it run, the code below only seems to work on the selected sheet. Is this how worksheet_calculate works or am I missing something else?

Your worksheet_calculate routine will only run when the sheet the worksheet_calculate routine is on is recalculated so you should place the worksheet_calculate routine under the sheet that BA is linking to.

That's the way worksheet_calculate works. If for some reason you don't want the routine under the same sheet as the sheet the data is sent to you'd need to force the sheet with the worksheet_calculate to do some calculation, something as simple as entering =Sheet1!A1*1 in a spare cell should be sufficient, where Sheet1 is your sheet receiving BA data and A1 is some cell containing a number. Hopefully that makes sense.

[keith.wheeldon]

Hi Tanden

That's worked a treat, thank-you for taking the time to help me. I really appreciate it. I just wish I'd asked the question on here earlier before spending two weeks trawling the net for answers. Thanks again Tanden.

Hi Spreadbetting

Thanks for your reply.
I know my issue is sorted but I like to learn and understand everything I can about vba. So do you mean that if I connect sheet1 to betangel and I put =Sheet1!A1*1 into a cell on sheet2, that every time A1 changes value on sheet1, Worksheet_calculate will get called on sheet2.? Or have I misunderstood what you meant?

[spreadbetting]

Yes, as long as you'd entered your worksheet_calculate VBA code under sheet2 or a separate worksheet_calculate routine under sheet2. A recalculation on sheet2 won't trigger the VBA if its entered under sheet1

The worksheet_calculate only fires if the sheet the code is entered under recalculates that why you add the times 1, you also have worksheet_change if you wanted to fire from when a cell value changes.

[nigelk]

It will depend on what result you have in zy3. If your formula actually returns True/False then it should work ok.

If you are returning a boolean result (true/false, yes/no, 1/0) then your code should be:

Code: Select all

If Range("ZY3") Then
PlaceBet
End If

On the attached in sheet 1 it will only fire if the formula result is actually True. On sheet 2 it will fire with both true/false, 1/0 answers.

Anyway, if you have 10 sheets, put this code in each sheet

Code: Select all

Private Sub Worksheet_Calculate()

ws = Me.Name

If Range("ZY3") Then
PlaceBet
End If

End Sub

and just enter your main code into module 1

Code: Select all

Public ws As String



Sub PlaceBet()

'my code here............

MsgBox "Calculation called for " & ws & " Sheet"


With Sheets(ws)

.Range("AAA1") = "1 BET  PLACED"
.Range("AAA2") = "BET CANCELLED"


End With

End Sub

[keith.wheeldon]

Thanks for your time spreadbetting.
It's really useful to know how and what code does when it's executed. Online examples are great for letting you know what a snippet of code does. However it seems difficult to get info on how it works. So once again thank-you.