Kick user out of a cell during "DoEvents"

user_3898584 0
Viewed 162

I there a way of using VBA to hit enter or down or select another cell, while a user is editing a cell? I am creating a quiz for children and want to ensure that the child doesn't press enter after typing their answer the code doesn't break.

Sorry if this is unclear I am new to VBA.

I am trying to create a 5 times table quiz for some students. The aim of the macro is to post the question then allow them 6 seconds to answer and then a 3 second rest.

As the user will be children I am hoping to make it as bulletproof as possible. To do this I was planning on the following:

-Most of the sheet will be protected so they can do very little.

-The answer cell is automatically selected so the kids only need to enter the number.

-At the end of the 6 seconds the code kicks them out of the answer cell and answer is checked.

I am struggling with the final bullet, specifically the kicking the students of of the answer cell when they are still editing it. I have tried: Application.SendKeys "{ENTER,False}".

Sub Test()

Dim PauseTime, BreakTime, time1, time2, time3, i
i = 1
Do While i < 13
    Sheets("Test_SG").Range("N_1").Value = i
    Sheets("Test_SG").Range("N_2").Value = 5    
    Sheets("Test_SG").Range("Answer").Select
    PauseTime = 6    ' Set question duration.
    BreakTime = 3    ' Set break duration.
    time1 = Timer    ' Set start time.
    time2 = Timer + PauseTime
    time3 = Timer + PauseTime + BreakTime
    Do Until time1 >= time2
        DoEvents ' Yield to other processes.
        time1 = Timer   
    Loop
    Application.SendKeys "{ENTER,False}"
    If Sheets("Test_SG").Range("Answer") = i*5 Then
        Sheets("Teacher_Zone").Range("Start").Offset(0,i).Value = "Y"
    Else
        Sheets("Teacher_Zone").Range("Start").Offset(0,i).Value = "N"
    End If
    Sheets("Test_SG").Range("A1").Select
    Do Until time1 >= time3
        time1 = Timer
    Loop
i = i + 1
Loop

End Sub
vba excel
2 Answers

You're getting some pushback here ("why use excel?") because Excel is very, very protective of its users during input. Seizing control during input into the sheet (or an input box in a form) is nearly impossible. I couldn't find a suggestion in a fair bit of searching, and "OnTime" events are processed only after an inputting user hits ENTER.

Having to go to the extent of the second comment and control every keystroke IS possible, but is definitely doing it the hard way, taking over the job you'd normally let Excel handle. Might as well write it in Python or something - maybe create an active web page solution if you've got access to a server.

You can stay in Excel easily if you reconsider your basic design: would this really be bad pedagogy if it were multiple choice? You can put in a LOT of choices, like every number.

Fill a bunch of cells with possible answers, in numerical order so the kid can find "37" quickly. A spreadsheet can show 1-100 in a 10x10 matrix compactly and give 100 multiple choices. Just set up an OnClick worksheet event - if the kid clicks on the right cell, yay, otherwise error. And an OnTime event after six seconds cancels the OnClick event and pops up a Too Late message.

Solved in a couple of lines that way, and it works with what Excel is good at rather than fighting it.

user_46084610

I like Roy's suggestion for selecting an answer from a table of possible solutions for the child to click.

If you do want the children to type something in, you should consider creating a UserForm. This would allow you to do some nice touches, like show then a clock counting down their allotted 6 seconds, and could save their various answers into variables that can be marked, or otherwise pushed to you as the administrator at a later time. (perhaps saved into an hidden worksheet for example).

It would also stop your children from clicking in your "answer" cell, and simply typing in the question to generate the correct answer. 6s is a long time to type it out! Data validation rules will not prevent your children from cheating using a formula.

Phil

user_79253010
Related