create automatic response to questions in excel....

  • Thread starter Thread starter felicia
  • Start date Start date
F

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
 
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
Click to expand...

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
 
ok... sounds easy but where to i enter the code?

Ken Johnson said:
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
Click to expand...
 
ok... sounds easy but where to i enter the code?
Click to expand...

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
 
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:
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?
Click to expand...

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

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