want to create a scoreboard in excel 1

[mudstuffin]

I am building a ‘quiz’ game in excel, which asks 20 randomly chosen questions from the list, and is run from a userform. It all seems to work ok, but now I want to store the players high scores in Sheet2, which are to be updated at the end of the game. How can I do this so that it adds the players name and score to the next row down from the last one..?

The players name comes from application.username & is assigned to ‘Name’, and the score comes from textbox6.

Ideally, I would also like it to update a players high score if they have already played the game & a score was entered by matching against their Name. This will prevent a potentially long list of the same players’ different scores. However, this part is not as important as getting the scoreboard running in the first place.

Once I’ve figured this out, I will sort the data by score, and then display it in another userform to be viewed by the players.

Thanks in anticipation.



mudstuffin.

 

[xlbo]

summat like this ?

lRow = sheets("Scores&quot.range("A65536&quot.end(xlup).row
with sheets("Scores&quot
fCell = sheets("Scores&quot.columns("A&quot.find (Name, lookin:=xlvalues)
if not fCell is nothing then
mRow = fCell.row
oScore = .range("B" & mRow).value
nScore = userformname.textbox6.value
if nScore > oScore then
.range("B" & mRow).value = nScore
else
end if
else
.range("A" & lRow +1).value = Name
.range("B" & lRow +1).value = _ userformname.textbox6.value
end if
end with

This will put names in Col A and scores in Col B on a sheet called "Scores" Rgds
~Geoff~

 

[mudstuffin]

Thanks Geoff.

I have added this in, but it trips up with the 'Unable to get the Find property of the range class' error.

What am I doing wrong or is there something else that I need to do. I have tried just putting it into a new workbook (to make sure it wasnt getting confused with any of my previous code) with a sheet named scores, and running this sub from a commandbutton on a userform (userform1), but same thing.

Am I missing something...??!!

Thanks again,



mudstuffin

 

[xlbo]

Apologies - just wrote this on the fly without testing, try this :
set fCell = .columns("A&quot.find (Name, lookin:=xlvalues)

also:
.range("B" & lRow +1).value = userform1.textbox6.value

HTH Rgds
~Geoff~

 

[mudstuffin]

Cheers Geoff. You da man !

That works exactly how I wanted it too.

Perhaps you can help with another little query....??

I want to run a timer from when the user selects a new question, to only give them say, 10 seconds to answer (its multiple choice). I tried using the ontime way, which will trigger the time up sub fine, but this way, the user cant see the time counting down, which would be naturally better. Any suggestions would be appreciated.

Thanks again.


mudstuffin.

 

[xlbo]

You will need 2 subs - one to kick off and then one to decrement the time - this works on sheet1 with the timer in A1. If on a userform, I would suggest using a label and changing the "caption" property instead of the range value
:
Sub startClock()
Sheets("sheet1&quot.Range("A1&quot.Value = 11
TenSecs
End Sub

Sub TenSecs()

With Sheets("Sheet1&quot.Range("A1&quot
If .Value = 1 Then
MsgBox "Time Up"
Exit Sub
Else
.Value = .Value - 1
End If
End With
NextTime = Now + TimeValue("00:00:01&quot

Application.OnTime NextTime, "tensecs"

End Sub

Start the process by running "StartClock" Rgds
~Geoff~

 

[Baggie]

Mudstuffin,
Would you be willing to share your quiz game app? I've been wanting to make a quiz app for my daughter to help her with her math skills.

Thanks much!

 

[mudstuffin]

Thanks Geoff.

That works nicely.

What code would I use to stop the countdown, as if a question is answered before the ten seconds run out, I want to then go on to the next part without the Time Up sub running when the time is, err, up...!


mudstuffin.

(Baggie, I will be happy to share it. It isn't quite finished yet but when it is, I'll let you know. Perhaps you can let me know your email address & I'll send you a mail when it's done ??)

 

[Baggie]

Mudstuffin,

Much obliged! I look forward to seeing it. My e-mail is steve.haackABC123@target.com Take out the ABC123 and that is my e-mail address.

Thanks again!

 

[xlbo]

Is this on a worksheet or userform..... what event indicates that the question has been answered ?? Rgds
~Geoff~

 

[mudstuffin]

Thanks Geoff.

It's on a userform. The user chooses the answer (one of four checkboxes), then clicks a button which runs a sub to test for the answer, which then updates scores etc.

Is that enuff info....?

mudstuffin.

 

[xlbo]

Hmm - little tricky this'n.
I'd suggest that your click event changes a value in a hidden label / textbox
You then need to amend the countdown sub to check for the value of this hidden label / textbox
Sub TenSecs()
If textbox1.value = 1 then'answer given
exit sub
else
end if
With Sheets("Sheet1&quot.Range("A1&quot
If .Value = 1 Then
MsgBox "Time Up"
Exit Sub
Else
.Value = .Value - 1
End If
End With
NextTime = Now + TimeValue("00:00:01&quot

Application.OnTime NextTime, "tensecs"

End Sub

the code that runs when you click to answer the question should reset the value to 0 as the last thing it does (ie just before the next question Rgds
~Geoff~

 

[mudstuffin]

Thanks Geoff.

Here's what I've done.

I placed:-

if textbox1.value=0 then exit sub

at the start of the countdown sub,

and to the click button event (to check the answer), I added

textbox1.value=0

and then added textbox1.visbile=true when the countdown dtarts, and visible=false when it ends, to make it look a bit better.

Seems to do the job

Cheers for all your help on this.



mudstuffin