Marco for reseting a spreadsheet without using a button

[DNaz]

Hello,

I posted on here a couple of years ago asking help for a darts spreadsheet I was making. At the time I was working on a portion of it for the dart game Cricket, but now I am trying to edit the X01 game sheets in the spreadsheet. I did a search and saw a few older threads asking a similar problem with sets and legs which did help me out for some parts but I was hoping to get something different set up.

For those that don't play darts, the way "legs" and "sets" work is the same principle as in tennis. You play a number of "legs" which add up to one "set". Darts would play a game of 501 and the first person to zero would get 1 added to their "legs" column. Then, the first person to a defined number of "legs" would win the "set". Then the first person to a defined number of "sets" woud win the match. Like in tennis, you play a game to 40+ and the winner would go up 1 game to 0, with the first to 6 games would win the "set". Most women's tennis matches are best of 3 sets and men's are best of 5 sets. So you would see the final scores in a men's match as 6-2, 3-6, 6-3, and 6-1. Clear as crystal or clear as mud?

Here is my problem:

I currently have a macro created that will count the legs and sets of a match when I push a button and even reset the sheet to begin a new leg. The only thing I'd like now is to get rid of the button and have the macro run in the background automatically everytime a score is input. I have a set up for counting "tons" (ie. scores over 100) using the Worksheet_Change function and I want to run the 'Winner' macros I created. Here are my codes:

Sub Winner1() 'This code is for the button to reset the fields and add legs for player 1. Winner 2 does the same but replaces 1 to 2 and O to Q in lines 1, 2, 5, 7, 8, 9 and 11.
    Dim Leg1 As Range, Set1 As Range, LegsTotal As Range, SetsTotal As Range
    Set Leg1 = Range("O12"): Set Set1 = Range("O14")
    Set LegsTotal = Sheets("Main Page").Range("C9"): Set SetsTotal = Sheets("Main Page").Range("G9")
    Application.EnableEvents = False
    If Set1.Value = SetsTotal Then Exit Sub
    Else
    If Leg1.Value = LegsTotal - 1 Then
    Set1.Value = Set1.Value + 1
    Leg1.Value = 0
    Else
    Leg1.Value = Leg1.Value + 1
    Range("O18:O117,Q18:Q117").Select
    Range("Q18").Activate
    Selection.ClearContents
    Range("O18").Select
    Application.EnableEvents = True
    End If
    End If
End Sub

'This code is on the worksheet and currently runs to count the 100+ scores
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("O18:O117")) Is Nothing Then
            Run "TonPlus1"
    End If
    
    If Not Intersect(Target, Range("Q18:Q117")) Is Nothing Then
            Run "TonPlus2"
    End If
End Sub

I just want to add a line to the Worksheet_Change macro that when a cell in the Range("M18:M117") = 0 to run the 'Winner1' or 'Winner2' marcos. Or even in the 'Winner1' or 'Winner2' marcos if required.

Any help someone could provide would be greatly appreciated. Any questions, feel free to ask.

 

[DNaz]

I just realized that my code for 'Winner1' is wrong. Correct code as follows:

Sub Winner1()
    Dim Leg1 As Range, Leg2 As Range, Set1 As Range, LegsTotal As Range, SetsTotal As Range
    Set Leg1 = Range("O12"): Set Leg2 = Range("Q12"): Set Set1 = Range("O14")
    Set LegsTotal = Sheets("Main Page").Range("C9"): Set SetsTotal = Sheets("Main Page").Range("G9")
    Application.EnableEvents = False
    If Set1.Value = SetsTotal Then Exit Sub
    If Leg1.Value = LegsTotal - 1 Then
    Set1.Value = Set1.Value + 1
    Leg1.Value = 0
    Leg2.Value = 0
    Else
    Leg1.Value = Leg1.Value + 1
    Range("O18:O117,Q18:Q117").Select
    Range("Q18").Activate
    Selection.ClearContents
    Range("O18").Select
    Application.EnableEvents = True
    End If
End Sub

 

[DNaz]

Actually... The more I think about it, the more I think it'll be just as easy to click the button to reset the columns.

Forget about it.