create automatic response to questions in excel....

[felicia]

Hi,
Is there a way in excel where I have 10 questions and can click on one
question and get the answer to pop up in my spreadsheet?

thank you,

Felicia

 

[Ken Johnson]

Hi,
Is there a way in excel where I have 10 questions and can click on one
question and get the answer to pop up in my spreadsheet?

thank you,

Felicia

One way, with questions in A1:A10 and corresponding correct answers in
A1:A10 on a hidden worksheet with tab name "Answers"...

right click the tab of the sheet with the questions then select "View
code" from the popup menu and paste in the following code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Cells.Count = 1 And _
Not Intersect(Target, Range("A1:A10")) Is Nothing And _
ActiveCell.Value <> "" Then
Application.EnableEvents = False
MsgBox Sheets("Answers").Range(Target.Address).Value
Range("A11").Select
Application.EnableEvents = True
End If
End Sub

When you select a cell with a question the code produces a message box
with the correct answer.

Ken Johnson

 

[felicia]

ok... sounds easy but where to i enter the code?

One way, with questions in A1:A10 and corresponding correct answers in
A1:A10 on a hidden worksheet with tab name "Answers"...

right click the tab of the sheet with the questions then select "View
code" from the popup menu and paste in the following code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Cells.Count = 1 And _
Not Intersect(Target, Range("A1:A10")) Is Nothing And _
ActiveCell.Value <> "" Then
Application.EnableEvents = False
MsgBox Sheets("Answers").Range(Target.Address).Value
Range("A11").Select
Application.EnableEvents = True
End If
End Sub

When you select a cell with a question the code produces a message box
with the correct answer.

Ken Johnson

 

[Ken Johnson]

ok... sounds easy but where to i enter the code?

After you copy the code, right click the sheet tab of the sheet with
the questions. A popup menu appears with "View code" as one of the
options.
When you select that option the Visual Basic Editor opens up at the
Worksheet's code module.
Once that module has opened just hit paste to get the code in place.
Then either go File|Close and Return to Microsoft Excel or use the key
combination Ctrl + F11 to get back to the normal Excel interface.
For the code to work your workbook security level will have to be
Medium, and macros have to be enabled. If this is not the case, then
go Tools|Macro|Security... and select the Medium option|OK|Close. Then
open the workbook and click on "Enable Macros" on the "Security
Warning" dialog.

Ken Johnson
If

 

[felicia]

Ken:
im getting a "syntax error". My questions begin in "A3" and end in "A39". Im
not sure why the code isnt working. Can i email you the spreadsheet? Maybe by
looking at it you can help me figure out what im doing wrong?

 

[Ken Johnson]

Ken:
im getting a "syntax error". My questions begin in "A3" and end in "A39". Im
not sure why the code isnt working. Can i email you the spreadsheet? Maybe by
looking at it you can help me figure out what im doing wrong?

Sure, that's OK. Just get my gmail account from my profile.

Ken Johnson

 

[felicia]

Great. I really appreciate your help. I will email it tomorrow morning. Maybe
its the way my spreadsheet is set up.