Msgbox when cell value reaches and passes a certain value.

[Batty0810]

Hi all,

I am relatively new to Excel VBA coding and i have created a cricket scoring workbook. Everything is working excellently and now i am adding a few things that will make the scorebook more interesting and informative.

My question is this: When a player reaches milestones, like 50 runs, 100 runs etc, i would like a msgbox to popup with the relavent info (ie - "50 Runs" or "100 Runs").

If i use the following code, the msgbox pops up all the time:

If Cells(6,3).value = 50 then
msgbox "50 Runs"
Elseif Cells(6,3).value = 51 then
msgbox "50 Runs"
etc etc.

The scoring works like this:
A player can score anything from 1 to 6 runs off one ball, so a player can pass the 50-run mark by moving from 49 to 50, or from 48 to 52 or 47 to 53 or from 49 to 55.

What i would like to do is for a msgbox to appear just once when the player passes the 50 mark or the 100 mark etc.

I have seen this appear in other scoring applications but cannot figure out what the coding is to make this happen.

Your help and assistance would be greatly appreciated.

 

[Rick Rothstein]

My question is this: When a player reaches milestones, like 50 runs, 100 runs etc, i would like a msgbox to popup with the relavent info (ie - "50 Runs" or "100 Runs").

If i use the following code, the msgbox pops up all the time:

If Cells(6,3).value = 50 then
msgbox "50 Runs"
Elseif Cells(6,3).value = 51 then
msgbox "50 Runs"
etc etc.

The scoring works like this:
A player can score anything from 1 to 6 runs off one ball, so a player can pass the 50-run mark by moving from 49 to 50, or from 48 to 52 or 47 to 53 or from 49 to 55.

What i would like to do is for a msgbox to appear just once when the player passes the 50 mark or the 100 mark etc.

I'll start by saying I know absolutely nothing about Cricket, so I'll talk in generalities. In your macro, do you have the score before whatever you add takes it above 50, 100, etc.? If so, I'll call the variable that holds it OldScore and I'll call the variable holding the score after you add whatever it is you are adding NewScore. You can do your once only test like this...

If OldScore < 50 And NewScore >= 50 Then
  MsgBox "50 Runs"
ElseIf OldScore < 100 And NewScore >= 100 Then
  MsgBox "100 Runs"
ElseIf OldScore < 150 And NewScore >= 150 Then
  MsgBox "150 Runs"
etc. etc.

or, if you do not mind the math, this shorter code snippet should be able to handle any milestone...

If OldScore < 50 * Int(NewScore / 50) Then MsgBox 50 * Int(NewScore / 50) & " Runs"

 

[Batty0810]

Hi Rick,

Thank you for your reply. Perhaps a little more info from my side might help.

I am using buttons to create the scoring. So for example, i have buttons for 1 run, 2 runs, 3 runs, 4 runs and 6 runs. Because you always have two players batting together in cricket, I am using the following code to create the values:

'batting'
If Bat1.Value = True Then 'Bat1 is an option button'
'Runs'
myval = Val(Cells(6, 3).Value)
myval = myval + 1
Cells(6, 3).Value = myval
'Balls'
myval = Val(Cells(6, 4).Value)
myval = myval + 1
Cells(6, 4).Value = myval


ElseIf Bat2.Value = True Then
'Runs'
myval = Val(Cells(7, 3).Value)
myval = myval + 1
Cells(7, 3).Value = myval
'Balls'
myval = Val(Cells(7, 4).Value)
myval = myval + 1
Cells(7, 4).Value = myval

etc.

So could you elaborate on how i would add your above code in to my script?

Once i get it working and i can copy and paste it into the other button codes.

Thanks

 

[Rick Rothstein]

Hi Rick,

Thank you for your reply. Perhaps a little more info from my side might help.

I am using buttons to create the scoring. So for example, i have buttons for 1 run, 2 runs, 3 runs, 4 runs and 6 runs. Because you always have two players batting together in cricket, I am using the following code to create the values:

'batting'
If Bat1.Value = True Then 'Bat1 is an option button'
'Runs'
myval = Val(Cells(6, 3).Value)
myval = myval + 1
Cells(6, 3).Value = myval
'Balls'
myval = Val(Cells(6, 4).Value)
myval = myval + 1
Cells(6, 4).Value = myval


ElseIf Bat2.Value = True Then
'Runs'
myval = Val(Cells(7, 3).Value)
myval = myval + 1
Cells(7, 3).Value = myval
'Balls'
myval = Val(Cells(7, 4).Value)
myval = myval + 1
Cells(7, 4).Value = myval

etc.

So could you elaborate on how i would add your above code in to my script?

Repeating... I know nothing about Cricket, so some questions...

1) Is the +1 that I highlighted because this the "1 run" button that was clicked (so that it would be +2 if this were the code for the "2 runs" button)?

2) The first If..Then statement has the comment "Bat1 is an option button"... would the second If..Then statement be "'Bat2 is an option button"? If so, how many option buttons are there? Are Bat1, Bat2, etc. players?

3) Everything you posted (including the etc.) belongs to the code for the "1 run" button click, correct?

4) Runs and Balls are apparently different... is it the "runs" that are to be tested for crossing the milestone?

 

[Batty0810]

Hi Rick,

Right, to answer the questions:

1) Yes. The value you highlighted is for the Runs. So the "2 Runs" button would be 2 etc.

2) No. Each "Run Button (1, 2 etc)" has the same code in it. The only difference would be the change in value that you highlighted. The Option buttons do not matter in this case as the milestone in case would only be for the Option button which value = true.

3) Yes. As i stated above, each button (0, 1, 2, 3, 4, 5, 6) has the same code, just with value changes depending on how many runs are scored.

4) Yes. Only the runs are needed in the statement for the milestone.

Perhaps if i explain in an everyday term: Let's say the Milestone is $50 (per shift)
Let's say a person is working at a fast food outlet. (Player)
He charges for a softdrink - $10 (Runs)
Then he charges for a Burger - $30 (Runs) (Total now $40)
Then someone orders a salad for $8. Total is now $48.
If someone came in and ordered fries for $2 - not a problem because the total would be $50
But if someone came in and ordered a shake for say $5 - then the total would be $53.

So what i want to do is have a msgbox that pops up when the total reaches $50 or passes it. But it must not popup when it hits $50 and then again at $51 or $52 etc.

With me?

The variants in reaching a milestone in cricket are 5. When a player is on 49 runs, he could get 1 run to reach 50 or 2 runs to move to 51 (passes 50) or 3 to move to 52 and so on. the final variant is 49 + 6 runs which takes him to 55.

Thanks

 

[Batty0810]

Hi Rick,

Just thought i would let you know that i came right by using a Static Variable solution. Did some scouring and tried it and it worked perfectly.

Thanks for your help.