Scorer sheets

[ageordieinneed]

Hi all newbie here and not the best with excel or vba
i am doing a darts scorer with a few macro button(180,140,100) and vlookup (3 dart, 2 dart finishing) which seem to work great
so here goes
1, when 501(k21 or m21 reaches 0 it adds +1 value to i5 or o5 1st one to zero wins the 1 instead of pressing a win button on the player and resetts k21 and m21 to 501?
2, is there a way of making it call the numbers ie say i score/type 100 i hear it say 100 which i have all media file on pc

help much appreciated

 

[Snakehips]

Re: Scorer sheets help

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K5:K21,M5:M21")) Is Nothing Then Exit Sub
TCol = Target.Column
[COLOR=#ff0000]OtherCol = 11[/COLOR]
[COLOR=#ff0000]NameCol = 8[/COLOR]
[COLOR=#ff0000]If TCol = 11 Then[/COLOR]
[COLOR=#ff0000]OtherCol = 13[/COLOR]
[COLOR=#ff0000]NameCol = 14[/COLOR]
[COLOR=#ff0000]End If[/COLOR]


'Call the score
Select Case Target
Case 120, 140, 180
Application.Speech.Speak Target, SpeakAsync:=True
Case Else
End Select


If Cells(21, TCol) = 0 Then
Application.EnableEvents = False
GameCol = 9
If TCol = 13 Then GameCol = 15
Cells(5, GameCol) = Cells(5, GameCol) + 1
[COLOR=#ff0000]Range("K5:K20,M5:M20")[/COLOR].ClearContents
Application.EnableEvents = True
End If


'Call the finish
Select Case Cells(21, OtherCol)
Case 2 To 158, 160 To 161, 164, 167, 170
Do Until Timedelay = 200000
Timedelay = Timedelay + 1
Loop
Player = [COLOR=#ff0000]Cells(4, NameCol)[/COLOR] & "  ....., you require " & Cells(21, OtherCol)
Application.Speech.Speak Player, SpeakAsync:=True
Case Else
End Select


End Sub

 

[ageordieinneed]

Re: Scorer sheets help

Hi Snakehips
Thanks works perfect
my next problem ! bet you never looked at my post now
i have cells I6,8,10 and the same in O with how many 100s+ =COUNTIF(K5:K20,">99"),140+ 180's wth same formula in cells
and averages in i12 and o12 =IFERROR(AVERAGE(K5:K20),"0")
how do i go about keeping the count going instead of returning to zero after a player wins the legs until i press the new game button
so say we play best of 7 legs counts 100s so on and keep the ave?
sorry cheers

 

[Snakehips]

Re: Scorer sheets help

Will need to accumulate the counts etc using vba rather than formulas, then reset with the new game button.

Easy enough for anybody South of Tyneside.

With your stated formulas does a 180 also clock up as a 140+ and a 100+ and 140+ also a 100+ ????
And the average is a straight average score per visit ?

 

[ageordieinneed]

Re: Scorer sheets help

Haha Thanks snakehips
yep 180s,140,and 100 all seem to +
the average is 3 dart ave
cheers

 

[Snakehips]

Re: Scorer sheets help

Maybe like....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K5:K21,M5:M21")) Is Nothing Then Exit Sub
TCol = Target.Column
OtherCol = 11
NameCol = 8
[COLOR=#0000cd]StatCol = 15[/COLOR]
If TCol = 11 Then
OtherCol = 13
NameCol = 14
[COLOR=#0000cd]StatCol = 9[/COLOR]
End If


'Call the score
Select Case Target
Case 120, 140, 180
Application.Speech.Speak Target, SpeakAsync:=True
Case Else
End Select


[COLOR=#0000cd]'Update Stats[/COLOR]
[COLOR=#0000cd]If Target > 99 Then Cells(6, StatCol) = Cells(6, StatCol) + 1[/COLOR]
[COLOR=#0000cd]If Target > 139 Then Cells(8, StatCol) = Cells(8, StatCol) + 1[/COLOR]
[COLOR=#0000cd]If Target > 179 Then Cells(10, StatCol) = Cells(10, StatCol) + 1[/COLOR]
[COLOR=#0000cd]
[/COLOR]
[COLOR=#0000cd]Cells(7, StatCol) = Cells(7, StatCol) + Target[/COLOR]
[COLOR=#0000cd]Cells(9, StatCol) = Cells(9, StatCol) + 1[/COLOR]




If Cells(21, TCol) = 0 Then  'Leg is won
Application.EnableEvents = False
GameCol = 9
If TCol = 13 Then GameCol = 15
Cells(5, GameCol) = Cells(5, GameCol) + 1
Range("K3:K20,M3:M20").ClearContents
Application.EnableEvents = True
End If




'Call the finish
Select Case Cells(21, OtherCol)
Case 2 To 158, 160 To 161, 164, 167, 170
Do Until Timedelay = 200000
Timedelay = Timedelay + 1
Loop
Player = Cells(4, NameCol) & "  ....., you require " & Cells(21, OtherCol)
Application.Speech.Speak Player, SpeakAsync:=True
Case Else
End Select


End Sub

Formulas in I12 & O12
Assumes I7, I9 & O7 , O9 are otherwise unused and can be used for tally of points and visits. ????
Text in those cells can be set to white so effectively invisible. ????
If not possible then there are several other ways to do it.

If yes, then your New Game re-set code needs to include like....

Range("I6:I10,O6:O10") = 0

Excel 2010
	H	I	J	K	L	M	N	O	P
3					501
4	Steve Bruce						Wazzock
5	Games	0		13		140		4	Games
6	100+	0		24		137		6	100+
7	Points	234		17		180		2461
8	140+	0						4	140+
9	Visits	14						20
10	180	0						2	180
11
12	3-D Av	16.7143						123.05	3-D Av
13
14
15
16
17
18
19
20
21				447		44
Master

Cell Formulas
Range	Formula
I12	=IFERROR(I7/I9,"")
O12	=IFERROR(O7/O9,"")

 

[ageordieinneed]

Re: Scorer sheets help

Cheers Snakehips
Thanks but putting S.Bruce in!! suprised you didnt put Mike Ashley (most hated man in the NE)in as the other player lol,
That works great cheers
i'm thinking of Q5 now haha