Tallying Changes in a Cell

Hi bb,


Ok, I have written this code based on your data arrangement.
If you change your data layout you will have to adjust the code below accordingly !

Also, using Conditional Formatting accross different worksheets can be problematic so I have included that feature within the code.So you don't need any C.Formatting.The code will take care of that.

One important thing however is that you will have to reset all the cells each time you want to start a new questionaire.which is logical.However,for the code to work correctly from one session to another you can't just manually clear the cells in Column A and start again.You will have to do this via code assigned to a CommandButton.

So everytime you click this button everything is reset for a new round.


So let's get started:


Place a CommandButton1 from the ToolBox ToolBar Not from the Forms Bar on Sheet1.

Once the Button is placed and sized properly on the sheet, Click once on the Ruler Icon displayed on the Toolbox Bar to exit design mode.

Next, Right click on the worksheet tab, select View Code and paste the code below:


<font face=Courier New><SPAN style="color:#007F00">' Written By Jaafar On 10/07/03</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()

    <SPAN style="color:#007F00">' / / Resetting the Worksheet Ranges</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    CommandButton1.Caption = "Reset"
    <SPAN style="color:#00007F">With</SPAN> Sheets(1).Range("a2:a10")
        .Clear
        .HorizontalAlignment = xlCenter
        .Name = "Answers"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Sheets(2).Range("a2:a10")
     .Clear
     .HorizontalAlignment = xlCenter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#007F00">' / / Initialising the named range.</SPAN>
    <SPAN style="color:#007F00">' / / Uses cell V1 as a Dummy Cell .Change to suit.</SPAN>
    Range("v1").Name = "CorrectAnswersRange"
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'=================================================================</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#007F00">' / / Validation of user input.</SPAN>
    <SPAN style="color:#007F00">' / / If target's value already correct don't allow any more entries</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
     <SPAN style="color:#00007F">If</SPAN> Union(Target, Range("CorrectAnswersRange")).Address = _
                    Range("CorrectAnswersRange").Address <SPAN style="color:#00007F">Then</SPAN>
                    MsgBox "Correct answer already in cell !" & vbCrLf & vbCrLf & _
                    "Click the Reset button to start afresh.", vbCritical, "Warning !"
                    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
                    Application.Undo
                     Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Target
        <SPAN style="color:#00007F">If</SPAN> Union(Target, Range("Answers")).Address = Range("Answers").Address <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">If</SPAN> Union(Target, Range("CorrectAnswersRange")).Address <> _
                    Range("CorrectAnswersRange").Address <SPAN style="color:#00007F">Then</SPAN>
                        <SPAN style="color:#00007F">If</SPAN> UCase(Target) <> UCase(Sheets(2).Cells(.Row, .Column + 1)) <SPAN style="color:#00007F">Then</SPAN>
                            Sheets(2).Cells(.Row, .Column) = Sheets(2).Cells(.Row, .Column) + 1
                            Target.Interior.Color = vbRed
                        <SPAN style="color:#00007F">Else</SPAN>
                            Sheets(2).Cells(.Row, .Column) = Sheets(2).Cells(.Row, .Column) + 1
                            Names.Add "CorrectAnswersRange", Union(Range("CorrectAnswersRange") _
                            , Target)
                            Target.Interior.Color = vbGreen
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'=====================================================================</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("Answers")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Target
            <SPAN style="color:#00007F">If</SPAN> .Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "Multiple Selections <SPAN style="color:#00007F">Not</SPAN> Allowed!" _
                , vbCritical
                .Cells(1).<SPAN style="color:#00007F">Select</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



Press ALT+F11 to go back to the Excel Window and get your son to start entering his answers

Let me know if any problems.

Hope this helps.

you could also hide the sheet with the answers - format | sheet | hide.
easy top unhide - if it's a concern search the board for xlVeryHidden & see what you come up with, as in:

http://www.mrexcel.com/board2/viewtopic.php?t=56115&highlight=xlveryhidden

Using RECALL from the morefunc.xll add-in...

=IF(COUNTA(A1)+1,RECALL(TRUE)+1)

counts how many times the contents of A1 have changed, an example use of RECALL that Longre gives.

Wow- thanks for your replies. I am about to go to bed so I will try this out when I get up. I never did anything with codes so this will be interesting. Im looking now to find out exactly how to insert a command button on the worksheet( I assume it must be placed in a cell; I could be wrong..just learning as I go...)and then I'll give this a try. I will let you know how I make out. Thanks again for taking time to reply.

right click on the tool bars | select control tool box | click command button (solid rectangle) | click on the sheet | right click on the button | select view code | paste the code into the module that turns up (either delete the 2 lines of code that turn up, or only paste the bits of code between the equivalent lines in the suggested code - you can only have 1 click event routine) | close the code window | only the control toolbar, click the triangle button to exit design morde | close the toolbar

Thanks Paddy for the step-by-step...it helped me get though the steps.

Rafaaj2000,
I put in the code and gave it a test run. Truly amazing! It worked exactly the way I had envisioned !! I cant thank you all enough for your help. Aladin, I will read up on the link you sent; Id like to try to learn more on it.

Again, my thanks to you all for your time and help.

bb

Rafaaj2000 posted a code on this post that my kids now use for their schoolwork. Thanks again to Rafaaj2000 and all others who helped. My daughter just told me something this evening about how she is being timed as she does a certain amount of math problems in school. Id now like to add a timer feature to the program so she can practice at home. Can someone walk me through on what I need to do to set up a timer? I have searched and read some other posts on timers and I admit Im gun shy on trying any of them out because simply put: I wouldnt want to do anything that would somehow irreversibly mess up the program as it now stands. I was looking for something simple; say a command key on sheet 1 that acts as a start/stop with a cell that gives the time(tenths of a second would be nice).

Any help on this would again be greatly appreciated.

Thanks in Advance,

bb

Hi bb,
If you're concerned about messing up the workbook you have then before doing anything with it, (even just using it the way it was intended), save an extra copy of it. It's very likely you will at some point be glad you did.

Here's something you can try for a timer.
In cell A1 type in the word Start and in A2, type in Stop.
Now in your worksheet module paste this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column > 1 Then
Exit Sub
End If
If Target.Row > 2 Then
Exit Sub
End If

Target.Offset(0, 1) = Now
With Target.Offset(0, 1)
.Value = .Value
.Value = Format(Now, "h:mm:ss AM/PM")
End With

Target.Offset(0, 1) = Now
With Target.Offset(0, 1)
.Value = .Value
.Value = Format(Now, "h:mm:ss AM/PM")
End With

End Sub

Now when your kids are ready to start being timed they just click on Start (in A1) and the time will be recorded in cell B1. They answer their questions and then click on Stop when their done. The stop time will be recorded in cell B2.

Is this anything like what you're looking for?

(Oh yeah, I really like rafaaj2000's code too. Very cool.)

Dan

Sorry, That didn't come out quite right. The code to paste in your worksheet module should be this instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column > 1 Then Exit Sub
If Target.Row > 2 Then Exit Sub

Target.Offset(0, 1) = Now
With Target.Offset(0, 1)
.Value = .Value
.Value = Format(Now, "h:mm:ss AM/PM")
End With

End Sub

Hope this helps.
Dan