Counting DataValidation Attempts

[vanhunk]

Counting the number of times a person enters something a cell with data validation before entering a valid entry:

I have a list of cells, each with a different data validation list (single characters) based on the character in the cell next to it. I want to count the number of attempts for each cell until a correct entry is made. This is so that I can develop a score for the person trying to type the correct value into the cells. It is a young kid who is still getting used to the keyboard and I want to make learning the keyboard a bit more fun by putting a score to it.

I hope this is possible

Thank you so much!
Best Regards
Vanhunk

[Paul_Hossler]

Crude, but might give you some ideas.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim s As Variant
    Set r = Target.Cells(1, 1)
    If Intersect(r, Range("A2:A4")) Is Nothing Then Exit Sub
    s = r.Offset(0, 1).Value
    If r.Value <> s Then
        n = n + 1
        MsgBox "Wrong answer, that's " & n
        r.Select
    Else
        MsgBox "Good job, try another one"
        n = 0
    End If
End Sub

[vanhunk]

Hi Paul

Thank you very much, I am sure I can adapt it for my purposes. I will post the final once I have figured it out.

Best Regards
Vanhunk

[arnelgp]

you may also try to check the Cell.Validation

[vanhunk]

Thank you Arnelgp

I love it!

Regards
Vanhunk

[vanhunk]

Dear all

I have attached an example of the file that I want to use.

Note that each cell in the named "yellow" ranges has its own data validation, a list with one character and no drop-down.

I would like to score the person completing the cells and have the score written on the sheet. For each correct first attempt I want to add 2 to the score and with each wrong attempt subtract 1 from the score.

I believe some combination of Paul_Hossler's code and that of Arnelgp would do the trick, I could however not work it out.

I was thinking something like a combination of

 If Target.Value = Target.Validation.Value Then

and

 Intersect

with

 Private Sub Worksheet_Change(ByVal Target As Range) 

would work. I could unfortunately not figure out the how.

Any further assistance is much appreciated.

Regards
Vanhunk

[arnelgp]

check if the Score is correct.

[Aussiebear]

Very nicely done arnelgp.

[vanhunk]

Thank you arnelgp, much appreciated

Yes, the score is correct, but also not. I would like to subtract 1 for each incorrect attempt. The way it is set up is that you can't go further without putting in the correct character, thus without subtracting the incorrect attempts you will always get a full score. I believe that is the most challenging part.

I agree with Aussiebear, very nicely done. Now for the final challenge.

Best Regards
Vanhunk

[Aussiebear]

So, what happens if they continue to put in a wrong selection, do we continue to add 1 to the score?

[georgiboy]

How about the attached?

[vanhunk]

Aussiebear

As long as they enter the incorrect value, i.e. press enter or the arrow key (which will cause an error message), 1 must be subtracted from the score. When the correct character is finally entered, 1 must be added to the score.

[vanhunk]

Good day georgiboy

You have nailed it, it is doing exactly as I intended it to work!

Thank you guys I really appreciate all your effort and I think the result is brilliant!

Best Regards
Vanhunk

[georgiboy]

You are very welcome, we are always happy to help here.

[arnelgp]

alright.