Learning Excel #3, Designing Math Challenge For Young Children, Template #3

Standard

Hello again,

 

We have finally got some summer weather here in England and although many of you won’t associate summer with Excel designing, I have decided to keep bravely going strong with it even on these hot days. (Correction: The days are quite hot, but the weather isn’t exactly great for outdoor activities due to summer storms hanging in the air, ready to hit as soon as you would delve into some enjoyable outdoor activity.)j

 

This is my third attempt at creating a simple, clean and visually appealing math challenge template for young children and this is the first one where I had a play with automated macro.

 

During the designing process I had to ask my Excel guru for a little advice on handling error messages from VBA (so thank you Gary), but apart from that, it is all my work. Well, mine and Internet’s. But I managed to find all the answers myself. I even found and corrected an error in processing the automated macros, which was causing conflict with the selection of the next field for next user’s input. This is not to show that after a month of designing little Math challenges I am a know-it-all Excel expert, it’s simply my attempt to describe my learning development with as much details as I can, so it’s also a bit of storytelling and contains an evidence of my progress and skills, should someone someday ask my what my Excel skills are like.

 

So what have we got here?

 

1, The idea

 

Once again the aim was to create a visually appealing file for young children (our daughter is almost six) and try something new (automated macro). As I was searching for a way to do this, I came across the following command:

 

Private Sub Worksheet_Change (ByVal Target As Range)

 

I am not 100% sure what exactly it does (many of you surely know) but I used it to run automated macro. (Code to follow.) Since I am learning all of this as I go, I don’t want to give anybody any advice as I myself feel a bit unsure when it comes to VBA and commands, and the logic of statements.

But I will be happy to provide what I can.

During the designing of the long code I had a struggle with the order of “If statements”, but in the end I got there. So I guess I must be doing something right.

 

This time I designed the layout to match the character of Ferda, the ant, and I quite like the red and black Kurt Cobain sweater design of it.

 

The automated macro moves the user from cell where he provided the answer into a cell where a new answer is required until the last answer is provided.

It took me a long time to write the correct code (and sometimes I think there might be errors in it, but as far as I can tell – it works! Yay!)

I have written the code in a way that also allows the user to change the numbers in the equations manually, without breaking what it does (previously I didn’t consider the user’s request to be able to change things in the file, something my guru told me to always consider, and also making the code as flexible as possible).

 

Here’s an example of above:

 If Range(“D6”) = Range(“F6”) – Range(“B6”) Then
    Call Cell3

– This means that as long as user changes pre-populated numbers and keeps the empty space for answers, he can adjust the equations as he wishes.

2, How does it work?

 

~ Checking the answers

Unlike in previous challenge, the incorrect answer doesn’t unhide a picture (at the time of writing the code this seemed to be a lot of work and I wanted to create this file quickly. But this is listed as possible improvement.)

The answers are checked by macro and once the answer is checked, macro moves user to next cell. The macro that checks the answer also lifts the white rectangle cover off the “thumbs up Ferda” if the answer is correct.

 

~ Checking whether all answers are correct

As in previous challenge, this is achieved by calculating the difference between the answer provided by user and between the correct answer checked by Excel itself. If the difference is 0, the answer is correct.

Then all zeroes are counted in a  master cell, which if all answers are correct shows 10, then for some reason I added -10 to get 0. If master check shows 0 a macro enabled button with Show Reward is made visible, in the case that not all answers are correct, button to show reward remains invisible.

 

~ Commands/Macros

 

There’s 10 macros to lift the white background under which the picture of “Thumbs up Ferda” is hiding.

 

Then there’s a long code that calls the macros one by one as user makes his/her way down the challenge. This code is (at least for me), stupidly long and easy to get lost within, but ultimately it can be broken into ten “commandments” consisting of 3 logical statements below:

 

1, If the cell with answer is blank, do nothing, exit

2, If the cell contains the correct answer, call macro to life the picture in referencing cell and move user to next cell where answer is required

3, Don’t unhide anything, just move user to next cell where answer is required

 

Once all answers are provided, user is either rewarded by a “Congratulations” message and picture of happy Ferda, or a message with “I’m sure you’ll do better next time.” is given.

Pictures:

Ferda 1

Ferda 2

Ferda 3

Ferda 4

~ Coding

Here’s the coding for the challenge:

 

> > >  Sheet 1 Code:

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Range(“D2”) = Range(“F2”) – Range(“B2”) Then

Call Cell1

End If

If Range(“D2”) <> Range(“F2”) – Range(“B2”) Then

Range(“B4”).Select

End If

If Range(“B4”) = Range(“F4”) – Range(“D4”) Then

Call Cell2

End If

If IsEmpty(Range(“B4”).Value) = True Then

Exit Sub

End If

If Range(“B4”) <> Range(“F4”) – Range(“D4”) Then

Range(“D6”).Select

End If

If IsEmpty(Range(“D6”).Value) = True Then

Exit Sub

End If

If Range(“D6”) = Range(“F6”) – Range(“B6”) Then

Call Cell3

End If

If Range(“D6”) <> Range(“F6”) – Range(“B6”) Then

Range(“B8”).Select

End If

If IsEmpty(Range(“B8”).Value) = True Then

Exit Sub

End If

If Range(“B8”) = Range(“F8”) – Range(“D8”) Then

Call Cell4

End If

If Range(“B8”) <> Range(“F8”) – Range(“D8”) Then

Range(“D10”).Select

End If

If IsEmpty(Range(“D10”).Value) = True Then

Exit Sub

End If

If Range(“D10”) = Range(“F10”) – Range(“B10”) Then

Call Cell5

End If

If Range(“D10”) <> Range(“F10”) – Range(“B10”) Then

Range(“B12”).Select

End If

If IsEmpty(Range(“B12”).Value) = True Then

Exit Sub

End If

If Range(“B12”) = Range(“F12”) – Range(“D12”) Then

Call Cell6

End If

If Range(“B12”) <> Range(“F12”) – Range(“D12”) Then

Range(“D14”).Select

End If

If IsEmpty(Range(“D14”).Value) = True Then

Exit Sub

End If

If Range(“D14”) = Range(“F14”) – Range(“B14”) Then

Call Cell7

End If

If Range(“D14”) <> Range(“F14”) – Range(“B14”) Then

Range(“B16”).Select

End If

If IsEmpty(Range(“B16”).Value) = True Then

Exit Sub

End If

If Range(“B16”) = Range(“F16”) – Range(“D16”) Then

Call Cell8

End If

If Range(“B16”) <> Range(“F16”) – Range(“D16”) Then

Range(“D18”).Select

End If

If IsEmpty(Range(“D18”).Value) = True Then

Exit Sub

End If

If Range(“D18”) = Range(“F18”) – Range(“B18”) Then

Call Cell9

End If

If Range(“D18”) <> Range(“F18”) – Range(“B18”) Then

Range(“B20”).Select

End If

If Range(“B20”) = Range(“F20”) – Range(“D20”) Then

Call Cell10

End If

If Range(“Y22”) = 0 Then

ActiveSheet.Shapes(“Rectangle 3”).Visible = True

End If

If Range(“Y23”) = 10 And Range(“Y22”) <> 0 Then

ActiveSheet.Pictures(“Ferda 1”).Visible = False

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = True

End If

End Sub

 

> > > This Workbook code

 

Private Sub Workbook_Open()

 

Range(“D2”).Select

ActiveSheet.Pictures(“Pic 1A”).Visible = True

ActiveSheet.Pictures(“Pic 2A”).Visible = True

ActiveSheet.Pictures(“Pic 3A”).Visible = True

ActiveSheet.Pictures(“Pic 4A”).Visible = True

ActiveSheet.Pictures(“Pic 5A”).Visible = True

ActiveSheet.Pictures(“Pic 6A”).Visible = True

ActiveSheet.Pictures(“Pic 7A”).Visible = True

ActiveSheet.Pictures(“Pic 8A”).Visible = True

ActiveSheet.Pictures(“Pic 9A”).Visible = True

ActiveSheet.Pictures(“Pic 10A”).Visible = True

ActiveSheet.Pictures(“Ferda 1”).Visible = True

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = False

ActiveSheet.Shapes(“Rectangle 3”).Visible = False

 

End Sub

 

> >> Macros to lift cover pictures

 

Sub Cell1()

‘ Cell1 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 1A”).Visible = False

Range(“B4”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell2()

‘ Cell2 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 2A”).Visible = False

Range(“D6”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell3()

‘ Cell3 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 3A”).Visible = False

Range(“B8”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell4()

‘ Cell4 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 4A”).Visible = False

Range(“D10”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell5()

‘ Cell5 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 5A”).Visible = False

Range(“B12”).Select

 

ErrorHandler:

Exit Sub

 

End Sub

Sub Cell6()

‘ Cell6 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 6A”).Visible = False

Range(“D14”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell7()

‘ Cell7 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 7A”).Visible = False

Range(“B16”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell8()

‘ Cell8 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 8A”).Visible = False

Range(“D18”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell9()

‘ Cell9 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 9A”).Visible = False

Range(“B20”).Select

 

ErrorHandler:

Exit Sub

End Sub

Sub Cell10()

‘ Cell10 Macro

 

On Error GoTo ErrorHandler

 

ActiveSheet.Pictures(“Pic 10A”).Visible = False

 

ErrorHandler:

Exit Sub

End Sub

 

> > > Show Reward macro

 

Sub ShowReward()

‘ ShowReward Macro

 

ActiveSheet.Pictures(“Ferda 1”).Visible = False

ActiveSheet.Pictures(“Ferda 2”).Visible = True

ActiveSheet.Pictures(“Ferda 3”).Visible = False

Range(“A1”).Select

 

End Sub

 

> > > Start Again Macro

Sub StartAgain()

‘ StartAgain

 

Selection.ClearContents

Range(“D2”).Select

Selection.ClearContents

Range(“B4”).Select

Selection.ClearContents

Range(“D6”).Select

Selection.ClearContents

Range(“B8”).Select

Selection.ClearContents

Range(“D10”).Select

Selection.ClearContents

Range(“B12”).Select

Selection.ClearContents

Range(“D14”).Select

Selection.ClearContents

Range(“B16”).Select

Selection.ClearContents

Range(“D18”).Select

Selection.ClearContents

Range(“B20”).Select

Selection.ClearContents

Range(“D2”).Select

Range(“D2”).Select

ActiveSheet.Pictures(“Pic 1A”).Visible = True

ActiveSheet.Pictures(“Pic 2A”).Visible = True

ActiveSheet.Pictures(“Pic 3A”).Visible = True

ActiveSheet.Pictures(“Pic 4A”).Visible = True

ActiveSheet.Pictures(“Pic 5A”).Visible = True

ActiveSheet.Pictures(“Pic 6A”).Visible = True

ActiveSheet.Pictures(“Pic 7A”).Visible = True

ActiveSheet.Pictures(“Pic 8A”).Visible = True

ActiveSheet.Pictures(“Pic 9A”).Visible = True

ActiveSheet.Pictures(“Pic 10A”).Visible = True

ActiveSheet.Pictures(“Ferda 1”).Visible = True

ActiveSheet.Pictures(“Ferda 2”).Visible = False

ActiveSheet.Pictures(“Ferda 3”).Visible = False

ActiveSheet.Shapes(“Rectangle 3”).Visible = False

 

End Sub

 

~ Possible improvements

 

1,

Assign an action/macro to run when incorrect answer is provided

 

2, Randomised equations each time challenge is restarted

 

 

~

 

 

Download:

Full Excel file can be downloaded from here >>>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s