Hi I'm new here and hope someone can help.

I want to create a game in excel for my Primary school class where a
random number between 1-100 is generated and they guess the number.
They will get the response too high, too low, or correct, well done.
The thing is I want to be able to track the number of guesses they
make. I would also like the sheet to reset when they click a cell.

I don't know much about Macros so any help would be very useful!

Kind regards,
Honey.

I suggest a Command Button to reset the number. You can use something
like the following to generate the number:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A1") = Int(1 + 100 * Rnd())
End Sub

Hth,
Merjet

I replied too quickly. Both Subs go in the code module for the
worksheet. Thinking about it, you probably want the random number in
some distant cell, because if the cell is selected, the number is
visible in the formula bar.

Private Sub CommandButton1_Click()
Range("A1") = Int(1 + 100 * Rnd())
Range("A1").Font.ColorIndex = 2
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static iCt As Integer
If Target.Address = "$B$1" Then
If Target Range("A1") Then
MsgBox "Too high."
iCt = iCt + 1
ElseIf Target < Range("A1") Then
MsgBox "Too low."
iCt = iCt + 1
Else
MsgBox "Correct with " & iCt & " guesses!"
iCt = 0
End If
End If
End Sub

Hth,
Merjet

On Apr 2, 9:01 am, "Honey" wrote:
Hi I'm new here and hope someone can help.

I want to create a game in excel for my Primary school class where a
random number between 1-100 is generated and they guess the number.
They will get the response too high, too low, or correct, well done.
The thing is I want to be able to track the number of guesses they
make. I would also like the sheet to reset when they click a cell.

I don't know much about Macros so any help would be very useful!

Kind regards,
Honey.

I sent a workbook example to you in email.
You can play with it and let me know what you think...

For everyone else, I use a command button to show a userform, it also
resets the number and hides it. The userform has a textbox, a guess
button and an exit button.
The guess button takes the number entered in the textbox, checks it
against the generated number and gives feedback. It adds 1 to the
guess count which it displays in a msgbox when they've guessed right.

Userform code
__________________
Option Explicit
Public x As String

Private Sub CommandButton2_Click()
Call ThisWorkbook.reset
Me.Hide
End Sub

Private Sub CommandButton1_Click()
x = TextBox1.Value
Dim y
If x = [a1].Value Then
MsgBox "Cool, You Guessed Right!!!"
y = [a2].Value
If y = "" Then
y = 1
Else
y = [a2].Value + 1
End If
MsgBox "It only took you " & y & " guesses!"
Call ThisWorkbook.reset
Else
If [a1].Value x Then
TextBox1.Value = "Too Low!"
TextBox1.SetFocus
Else
TextBox1.Value = "Too High!"
TextBox1.SetFocus
End If
[a2] = [a2] + 1
y = [a2].Value
'MsgBox "Guess Number " & y & "!"
x = 0
End If
End Sub
________________

ThisWorkbook module code
________________

Option Explicit

Private Sub Workbook_Open()
Call start
End Sub
Sub reset()
'sets cell A1 to a number between 1 and 100 and blacks out the cell
Range("A1").Select
Selection.Interior.ColorIndex = 1
[a1] = [INT(rand()*100)+1]
[a2].Value = 0
[b2].Select
End Sub
Sub start()
UserForm1.Show
Call reset
End Sub

Thanks Merjet I will have a look at see if I can work it out - you
have been a great help.

Rob - Thanks for your workbook - I have replied to your email.




On 2 Apr, 16:27, "okrob" wrote:
On Apr 2, 9:01 am, "Honey" wrote:

Hi I'm new here and hope someone can help.

I want to create a game in excel for my Primary school class where a
random number between 1-100 is generated and they guess the number.
They will get the response too high, too low, or correct, well done.
The thing is I want to be able to track the number of guesses they
make. I would also like the sheet to reset when they click a cell.

I don't know much about Macros so any help would be very useful!

Kind regards,
Honey.

I sent a workbook example to you in email.
You can play with it and let me know what you think...

For everyone else, I use a command button to show a userform, it also
resets the number and hides it. The userform has a textbox, a guess
button and an exit button.
The guess button takes the number entered in the textbox, checks it
against the generated number and gives feedback. It adds 1 to the
guess count which it displays in a msgbox when they've guessed right.

Userform code
__________________
Option Explicit
Public x As String

Private Sub CommandButton2_Click()
Call ThisWorkbook.reset
Me.Hide
End Sub

Private Sub CommandButton1_Click()
x = TextBox1.Value
Dim y
If x = [a1].Value Then
MsgBox "Cool, You Guessed Right!!!"
y = [a2].Value
If y = "" Then
y = 1
Else
y = [a2].Value + 1
End If
MsgBox "It only took you " & y & " guesses!"
Call ThisWorkbook.reset
Else
If [a1].Value x Then
TextBox1.Value = "Too Low!"
TextBox1.SetFocus
Else
TextBox1.Value = "Too High!"
TextBox1.SetFocus
End If
[a2] = [a2] + 1
y = [a2].Value
'MsgBox "Guess Number " & y & "!"
x = 0
End If
End Sub
________________

ThisWorkbook module code
________________

Option Explicit

Private Sub Workbook_Open()
Call start
End Sub
Sub reset()
'sets cell A1 to a number between 1 and 100 and blacks out the cell
Range("A1").Select
Selection.Interior.ColorIndex = 1
[a1] = [INT(rand()*100)+1]
[a2].Value = 0
[b2].Select
End Sub
Sub start()
UserForm1.Show
Call reset
End Sub

I have one more here, it uses input ad message boxes. Hope it is of some help:


Sub guessingGame()

Application.ScreenUpdating = False

timesToGo = 10
howMany = 1
gotIt = False

theNumber = Int(100 * Rnd() + 1)


For i = 1 To timesToGo

Do

guess = InputBox( _
"Please enter a number between 1 and 100", _
"Guess no:" & howMany)

Loop While Not IsNumeric(guess)

If CInt(guess) = theNumber Then

MsgBox "You guessed! The number is " _
& theNumber & vbCrLf & vbCrLf _
& "Number of guesses: " & howMany
gotIt = True
Exit For

ElseIf CInt(guess) theNumber Then

MsgBox "Your guess is too high."
howMany = howMany + 1

Else

MsgBox "Your guess is too low."
howMany = howMany + 1

End If

Next

If gotIt = False Then
MsgBox "The number was " & theNumber, , "Sorry!"
End If


End Sub


--
urkec


"Honey" wrote:



Thanks Merjet I will have a look at see if I can work it out - you
have been a great help.

Rob - Thanks for your workbook - I have replied to your email.




On 2 Apr, 16:27, "okrob" wrote:
On Apr 2, 9:01 am, "Honey" wrote:

Hi I'm new here and hope someone can help.

I want to create a game in excel for my Primary school class where a
random number between 1-100 is generated and they guess the number.
They will get the response too high, too low, or correct, well done.
The thing is I want to be able to track the number of guesses they
make. I would also like the sheet to reset when they click a cell.

I don't know much about Macros so any help would be very useful!

Kind regards,
Honey.

I sent a workbook example to you in email.
You can play with it and let me know what you think...

For everyone else, I use a command button to show a userform, it also
resets the number and hides it. The userform has a textbox, a guess
button and an exit button.
The guess button takes the number entered in the textbox, checks it
against the generated number and gives feedback. It adds 1 to the
guess count which it displays in a msgbox when they've guessed right.

Userform code
__________________
Option Explicit
Public x As String

Private Sub CommandButton2_Click()
Call ThisWorkbook.reset
Me.Hide
End Sub

Private Sub CommandButton1_Click()
x = TextBox1.Value
Dim y
If x = [a1].Value Then
MsgBox "Cool, You Guessed Right!!!"
y = [a2].Value
If y = "" Then
y = 1
Else
y = [a2].Value + 1
End If
MsgBox "It only took you " & y & " guesses!"
Call ThisWorkbook.reset
Else
If [a1].Value x Then
TextBox1.Value = "Too Low!"
TextBox1.SetFocus
Else
TextBox1.Value = "Too High!"
TextBox1.SetFocus
End If
[a2] = [a2] + 1
y = [a2].Value
'MsgBox "Guess Number " & y & "!"
x = 0
End If
End Sub
________________

ThisWorkbook module code
________________

Option Explicit

Private Sub Workbook_Open()
Call start
End Sub
Sub reset()
'sets cell A1 to a number between 1 and 100 and blacks out the cell
Range("A1").Select
Selection.Interior.ColorIndex = 1
[a1] = [INT(rand()*100)+1]
[a2].Value = 0
[b2].Select
End Sub
Sub start()
UserForm1.Show
Call reset
End Sub