Help with Darts spreadsheet

[DNaz]

Hello all, new to VBA/Marcos and need help with a function of a darts spreadsheet I'm developing. Did a google search and a few posts/threads from here came up so I figured I'd ask you guys for help.

I'm trying to create a sheet for a game of Cricket. For those of you that don't know how to play that game, you have to "close" the numbers 20 down to 15 as well as the bullseye by hitting each section three times. Throwing a dart into the Doubles area counts as 2 and the Triples area counts as 3, obviously. There is also a variation whereby if one player has closed a number and their opponent hasn't, then they can score points by hitting the closed number (I.E., if player one has closed the 20's and player 2 hasn't, each time player 1 throws a dart into the 20 area they get 20 added to their total score).

I downloaded a spreadsheet from another site that uses a dropdown list to select the number of hits and the score has to be entered manually, but I want to create a macro that does all this automatically. For example: I have six cells in one row like B4, B5, B6, B7, B8 and B9. B4/B7 have buttons in them, B5/B8 are blank, and B6/B9 have 0. I want to push the button in B4/B7 and have a 1 show in B5/B8. If the button is pressed again, a 2 shows in B5/B8 and if pressed a third time, a 3 shows. Then if pressed a fourth time, it stops the increment and checks the other cell to see if a 3 is there and if not then 20 is added to B6/B9 and if yes then nothing.

Now, I have adapted a code to do this but I don't know how to stop the increment nor to check the other cell. It is shown below. Any help someone here could provide would be greatly appreciated. I just hope what I'm asking isn't clear as mud.

Sub Button20_1()
Range("B5") = Range("B5") + 1
If Range("B5") = 4 = True Then
Range("B6") = Range("B6") + 20
End If
End Sub

 

[njimack]

Don't quite follow your explanation, but maybe something like:

Sub Button20_1()
With Range("B5")
If .Value<3 Then 
.Value = .Value + 1
Else
Range("B6").Value = Range("B6").Value + 20
End If
End With
End Sub

 

[EVR_99]

DNaz, I enjoy throwing darts does the spreadsheet you d/l let you decide what to shot at or does it make you go in deceding order (ie 20's 1st, 19 2nd, etc etc)? Are all shooters on the same talent level? If you could point me in the right direction I like to look at that original spreadsheet also.

Thanks,
David

 

[DNaz]

EVR, the spreadsheet I'm trying to make lets you throw at whichever number you want. You just press a button to record the hits. Here's the setup I'm trying to use...

 

[DNaz]

I should also mention I'm using Excel 2007.

 

[DNaz]

Don't quite follow your explanation, but maybe something like:

Sub Button20_1()
With Range("B5")
If .Value<3 Then 
.Value = .Value + 1
Else
Range("B6").Value = Range("B6").Value + 20
End If
End With
End Sub

Thanks. This worked for stopping the increment where I wanted it, but now I need to figure out a way for it to check the other cell and only add 20 when the other cell is less than 3.

 

[njimack]

Thanks. This worked for stopping the increment where I wanted it, but now I need to figure out a way for it to check the other cell and only add 20 when the other cell is less than 3.

If Range("B6").Value <3 Then Range.Value = Range("B6").Value + 20

 

[DNaz]

Thanks njimack, that works perfectly now. For the numbers anyway. I'm trying to figure out now how I can add in a input box to ask the player to input which area they hit in order to double the score in the addition. I've tried this one but it comes back with an error saying "End With without With"...

Sub ButtonDouble_1()
    With Range("B5")
    If .Value < 3 Then
    .Value = .Value + 1
    Else
    If Range("F5") < 3 Then
    Dim Message, Title, DubValue
    Message = "What Double did you hit? (Enter a value between 1 and 20)"
    Title = "Double"
    DubValue = InputBox(Message, Title)
    Range("D5").Value = Range("D5").Value + 2 * DubValue
    End If
    End With
End Sub

 

[njimack]

It often helps to indent your code - makes it easier to see the various With/End With, For/Next, If/End If statements...

Sub ButtonDouble_1()
    
    Dim Message As String
    Dim Title As String
    Dim DubValue As Integer
    
    
    With Range("B5")
        If .Value < 3 Then
            .Value = .Value + 1
        Else
            If Range("F5") < 3 Then
        
                Message = "What Double did you hit? (Enter a value between 1 and 20)"
                Title = "Double"
                DubValue = InputBox(Message, Title)
                Range("D5").Value = Range("D5").Value + 2 * DubValue
            End If
        End If
    End With
End Sub

Also, you might be better using Application.InputBox. The difference is subtle, but it allows you to specify the type of user entry (i.e date, string, integer, etc)

 

[DNaz]

Dude, you are a godsend. That code works perfect. My spreadsheet works exactly how I want it now. I wish I had a way on here to give you some reputation. Cheers.