activesheet.calculate

Posted by Farai on January 11, 2002 3:03 AM

Our class is trying to simulate a fruit machine, and we have a problem with activesheet.calculate. it seems that when a prize is allocated to a particular condition as shown below, the prize shows correctly but one 'chance' after it is suppossed to. the full code is below, hope you can help.
*****************************************************

Private Sub CommandButton1_Click()
Dim K, Totalwon, Bet As Integer

Totalwon = 0

prize = 999
If (Worksheets("Sheet1").Range("c8") = "Cherry") Then
prize = 10
End If

ActiveSheet.Calculate

Worksheets("Sheet1").Range("d12") = prize

Totalwon = Totalwon + prize
Bet = Bet + 1

Worksheets("sheet1").Range("C19") = Totalwon
Worksheets("sheet1").Range("C20") = Bet

End Sub

*****************************************************

Posted by Shylock on January 11, 2002 4:06 AM

Totalwon = Totalwon + prize Bet = Bet + 1 Worksheets("sheet1").Range("C19") = Totalwon Worksheets("sheet1").Range("C20") = Bet End Sub *****************************************************

Check whether this does what you need :-

Private Sub CommandButton1_Click()
Dim ResultCell As Range, PrizeCell As Range, TotCell As Range, BetCell As Range
Dim prize As Integer, Totalwon As Integer, Bet As Integer

With Worksheets("Sheet1")
Set ResultCell = .Range("C8")
Set PrizeCell = .Range("D12")
Set TotCell = .Range("C19")
Set BetCell = .Range("C20")
End With
prize = 10

If ResultCell.Value = "Cherry" Then
PrizeCell.Value = prize
TotCell.Value = TotCell + prize
BetCell.Value = BetCell + 1
Else:
PrizeCell.Value = 0
BetCell.Value = BetCell + 1
End If

End Sub

Posted by Gurlemph on January 11, 2002 12:18 PM

ActiveSheet.Calculate doesn't ever work (except in Excl 2002). To get around it, shut calc to manual- Application.Calculation=xlCalculationManual
Then calculate the used range of the worksheet -
activesheet.usedrange.calculate

Then toggle calc back to automatic

Application.Calculation=xlCalculationAutomatic