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

If you still have my previous code in the Worksheet code module then delete it and replace with the below.
If by any chance, your pre-existing code to move the cursor is using the Change event then we need to amalgamate the two as you can only have the one event!????

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K3:K21,M3:M21")) Is Nothing Then Exit Sub
TCol = Target.Column
If Not Cells(21, TCol) = 0 Then Exit Sub
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 Sub

 

[ageordieinneed]

Re: Scorer sheets help

hi snakehips
it works but get error message saying " run-time error '1004 cannot change part of a merged cell" its dont clear the cells ?

 

[ageordieinneed]

Re: Scorer sheets help

hi sorry snakehips
my bad i found that i had merged a cell which i have sorted thank you .
could youhelp with the caller ie saying 180 140??or is that a bit to complicated for me?
cheers

 

[Snakehips]

Re: Scorer sheets help

Hi, this one line edit to the code works for me to call all scores...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K3:K21,M3:M21")) Is Nothing Then Exit Sub
TCol = Target.Column


[COLOR=#ff0000]'Call the score[/COLOR]
[COLOR=#ff0000]Application.Speech.Speak Target, SpeakAsync:=True[/COLOR]




If Not Cells(21, TCol) = 0 Then Exit Sub
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 Sub

Or as below to just call say 120, 140, 180...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K3:K21,M3:M21")) Is Nothing Then Exit Sub
TCol = Target.Column


[COLOR=#ff0000]'Call the score[/COLOR]
[COLOR=#ff0000]Select Case Target[/COLOR]
[COLOR=#ff0000]Case 120, 140, 180[/COLOR]
[COLOR=#ff0000]Application.Speech.Speak Target, SpeakAsync:=True[/COLOR]
[COLOR=#ff0000]Case Else[/COLOR]
[COLOR=#ff0000]End Select[/COLOR]




If Not Cells(21, TCol) = 0 Then Exit Sub
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 Sub

 

[ageordieinneed]

Re: Scorer sheets help

thanks snakehips working prefect

 

[ageordieinneed]

Re: Scorer sheets help

Hi Snakehips sorry
Q3 how do i pick a random cell ie the cursor goes to m3 ro k3 at random when i press the new game button in a?
everything else works great that you've helped me with .
cheers

 

[Snakehips]

Re: Scorer sheets help

Hi, trigger something like the below....

Sub RandomCell()
Dim L As Double
Dim H As Double
L = 1
H = 1000
R = Int((H - L + 1) * Rnd() + L)
If R Mod 2 = 0 Then
Range("K3").Select
Else
Range("M3").Select
End If
End Sub

 

[ageordieinneed]

Re: Scorer sheets help

cheers snakehips
works great
better wait for q 4, haha
cheers

 

[Snakehips]

Re: Scorer sheets help

With player names in K3 and M3 maybe.....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("K3:K21,M3:M21")) Is Nothing Then Exit Sub
TCol = Target.Column
OtherCol = 11
If TCol = 11 Then OtherCol = 13


'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
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(2, OtherCol) & "  ....., you require " & Cells(21, OtherCol)
Application.Speech.Speak Player, SpeakAsync:=True
Case Else
End Select


End Sub

 

[ageordieinneed]

Re: Scorer sheets help

HI Snakehips GREAT Work
but i've make a mistake which ive sorted i hope,my subtractions starts k5 and m5
my name are in H and I4 and N,O 4 which are merged so it dont say the name