PDA

View Full Version : Solved: interactive test in excel 2003



neditheg
11-01-2010, 08:52 AM
hello,

I want to know if is possible to make an interactive test in excel.
The test must have at least 7 questions with 3 or 4 possible answers.
For example

Q.1 The Eiffel Tower it's from

a) Paris
b) London
c) Rome
d) Madrid

After chosing an answer any answer from this 4 possible ...the person who's taking the test ...should see the next question

Q.2 The capital of USA is

a) New York
b) Los Angeles
c) San Francisco
d) Washington


Then the test should pass to the next question

Q.3 .... and then Q.4 ... etc ..

Each good answer value is 1 point ...and a wrong answer value is 0 points.

At the end of the the text .. should appear a text box or something with the final result.

So ...

Q.x :hi:is this possible in excel 2003?

a) Yes --> what shloud I'll read to realize the test by myself or who's help shlould I ask;

b) No --> any tips for doing it ..in any way possible :) using sql ...or something.


Thanks!

Bob Phillips
11-01-2010, 12:45 PM
Try this

Kenneth Hobs
11-01-2010, 01:27 PM
What may not be obvious, since xld password protected the sheet, is that he used data validation.

In 2010, Data Validation is in the Data "menu". Choose List for the Allow box and select your Source range or hard code type it and separate each by a comma.

While you could put the answers in it, it is probably best not to. Use an external workbook macro to score the exam.

A more elaborate method is to use a userform but that takes more effort.

neditheg
11-02-2010, 01:12 AM
It's a good solution :) but need to understand how you did that :)

Bob Phillips
11-02-2010, 01:41 AM
What I did was to create a sheet of Answers which I made very hidden in the VBIDE. I hid the sheet so that the person cannot see the answers.

The Answers sheet has each question on a new row, with the correct answer in column A, choices in B:x. The answers are all names, like ans_Q1, ans_Q2 and so on. The ranges in B:x are all given names, such as B1:E1 has a name of list_Q1, B2:E2 has a name of list_Q2 etc., so that the person taking the test is not informed of Answers.

The Questionnaire uses Data Validation in C2 etc. to present the list, using a formula of =INDIRECT("list_"&$A2) and so on. The results flag is using a formula of =IF(C2="","",IF(C2=ans_Q1,"Correct","Wrong")). The sheet is password protected, using a password of xlTest, so that they cannot see that ormula, nor edit any cells except the DV lists.

neditheg
11-02-2010, 02:11 AM
And if I'm asking you :) to put an sample without hiding vital information for me :D :)

neditheg
11-02-2010, 02:16 AM
10x!

neditheg
11-02-2010, 02:28 AM
ok :) I've found the way to unhide the answer sheet! thanks!

Kenneth Hobs
11-02-2010, 06:14 AM
In your final product, you will probably want to consider only allowing one selection. Otherwise, they could keep picking until they got it right. Of course sometimes, that would be just fine.

Be sure to password protect your workbook, worksheets and vbaproject with a password more than 3 characters. It will give at least some minimal security.

Here is a method that I use to quickly hide or unhide a worksheet in the VBE.
Public pw As String

Sub SetPW()
pw = "ken"
End Sub

Sub HideDataSheet()
ThisWorkbook.Unprotect pw
Data.Visible = xlSheetVeryHidden
ThisWorkbook.Protect pw
End Sub

Sub UnHideDataSheet()
ThisWorkbook.Unprotect pw
Data.Visible = xlSheetVisible
ThisWorkbook.Protect pw
End Sub