Setting up Treasure Hunt spreadsheet

[Rachael]

Hello. I want to set up a simple locked down spreadsheet for a treasure hunt.
I want there the be an initial cell on the screen with a clue. I want there
to be some kind of button where they can click and put in a password/answer
and then the next cell will populate with the next clue.
I'm a novice user of excel - fine for basic tables and some forumlas, but
that's it.
I need to make this spreadsheet for work for next week (so, finishing it
this weekend for Monday) for International Talk Like a Pirate Day (Sept.
19th). Thanks!!

 

[Rachael]

I forgot to also mention - I know NOTHING about VB script or programming at
all. I tried creating a button in excel and it took me there. So unless
someone tells me what to write to make a button that has a password that
unlocks a cell - I can't figure it out on my own (and I don't think I can
learn VB in 3 days!)

 

[Otto Moehrbach]

Rachael
I can imagine something like this: You have a hidden sheet with a list
of all the clues, in order, in Column A. In Columns B,C,D,E you would have
4 possible answers/passwords for each clue. For each clue, one of those
answers is the correct one. In Column F of this hidden sheet, you would
have the correct answer.
On the main sheet, the sheet that the user sees, you have the first clue in
say, Column A. You also have a button at the top with frozen panes active
so that the button is always visible. Next to the button you would gave a
Data Validation cell.
The user clicks on the button. A macro will then fire that will set the 4
choices for that clue as the list in the DV cell. The user will then click
on the down-arrow of the DV cell and will see the 4 choices. He clicks on
his choice. The macro will then find the clue in Column A of the hidden
sheet and will check if the answer he gave matches the entry in Column F of
the hidden sheet. If it is, it will copy the next clue and paste it in that
first sheet and will reset the DV cell with the choices for that clue. If
his choice is wrong, the macro can display a message box telling him
whatever you want. You might also want a "START" button to reset everything
back to the first clue.
Does this sound anything like what you are looking for? HTH Otto

 

[Bob I]

Perhaps use Conditional Formatting to reveal(set font color black) the
next clue when the correct entry is placed in the "answer cell", and so

 

[Rachael]

Yes. This is what I want. I just don't know how to program the button to do
that at all. I know nothing about programming (except basic HTML)....

 

[Rachael]

That could work too -- as long as I can hide the answers and lock them. I
guess I would have to make the text the same colour as the background until
they enter the answer right, then it shows up?

But they would likely have to be single answers that way - wouldn't they?

 

[Rachael]

I was just trying this out, and i don't know how to make is so that if the
answer in cell A is X then the format of cell B is Y type thing. Conditional
formatting doesn't seem to work that way....

 

[Bob I]

Yes, and yes, multiple cells can be referenced in Conditional formatting
to give a multiple answer.

 

[Bob I]

Conditional format of B use Formula is. =A1=X so if A is equal to X then
format the font color of b.

 

[Otto Moehrbach]

Rachael
I have written a VBA driven file for you that does what you want, I
think. At least it will get you started. It consists of 3 macros in 3
different modules. You said that you are not familiar with VBA so it
wouldn't help you for me to post the macros. Send me an email and I'll send
you the file. We can then go from there. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto

 

[Damil4real]

Rachael
    I have written a VBA driven file for you that does what you want,I
think.  At least it will get you started.  It consists of 3 macros in3
different modules.  You said that you are not familiar with VBA so it
wouldn't help you for me to post the macros.  Send me an email and I'llsend
you the file.  We can then go from there.  My email address is
(e-mail address removed).  Remove the "extra" from this address.  HTH






- Show quoted text -

Can you post the macro on here so we can try it too? In case it's
something we might have to do as well.

Thanks!

 

[Otto Moehrbach]

I don't see my resaponse in my server so I'm sending it again. Otto

The file consists of a sheet named "Main" and a hidden sheet named "Clues".
The first macro goes in the sheet module of the Main sheet.
The second macro goes in the workbook module.
The third macro goes into a regular module.
The Clues sheet contains all the clues, as well as 4 possible answers for
each clue, only one of which is the correct answer. All the correct answers
are in Column F as well.
D1 of the main sheet is a DV cell that displays the 4 possible answers for
the current clue, which is displayed in Column B.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D1")) Is Nothing Then
If IsEmpty(Target.Value) Then Exit Sub
With Sheets("Clues")
If Target.Value <> .Range("TheAns").Value Then
MsgBox "Wrong answer. Try again.", 16, "WRONG"
Application.EnableEvents = False
Range("D1").ClearContents
Application.EnableEvents = True
Else
MsgBox "Correct! Continue to the next clue.", , "Correct"
End If
End With
End If
End Sub

Private Sub Workbook_Open()
With Sheets("Main")
If Not IsEmpty(.Range("B3").Value) Then
.Range("B3", .Range("B" & Rows.Count).End(xlUp)).ClearContents
End If
.Range("D1").ClearContents
End With
End Sub

Sub CluesAnswers()
Dim AllClues As Range
Dim LastClue As Range
Dim NextClue As Range
With Sheets("Clues")
Set AllClues = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
If IsEmpty(Range("B3").Value) Then
Range("B3").Value = .Range("A2").Value
.Range("B2").Resize(, 4).Name = "Possibles"
.Range("F2").Name = "TheAns"
Else
Set LastClue = Range("B" & Rows.Count).End(xlUp)
Set NextClue = AllClues.Find(What:=LastClue.Value).Offset(1)
If IsEmpty(NextClue.Value) Then
MsgBox "There are no more clues."
Exit Sub
End If
LastClue.Offset(1).Value = NextClue.Value
NextClue.Offset(, 1).Resize(, 4).Name = "Possibles"
.Cells(NextClue.Row, 6).Name = "TheAns"
Application.EnableEvents = False
Range("D1").ClearContents
Application.EnableEvents = True
End If
End With
End Sub

Rachael
I have written a VBA driven file for you that does what you want, I
think. At least it will get you started. It consists of 3 macros in 3
different modules. You said that you are not familiar with VBA so it
wouldn't help you for me to post the macros. Send me an email and I'll
send
you the file. We can then go from there. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH






- Show quoted text -

Can you post the macro on here so we can try it too? In case it's
something we might have to do as well.

Thanks!