Automatically fill a cell with predetermined

  • Thread starter Thread starter John
  • Start date Start date
J

John

Using Excel2000
I am setting up a survey with questions in column A
and responses in columns B & C.
Column B is for Yes, & column C is for NO

Is there any way to have the responses automatically put in the cells
when the user clicks on the appropriate cell.

i.e.
COLUMN A COLUMN B COLUMN C
Question1 Yes
Question2 No
Question3 Yes
Question4 Yes

I would like the responses automatically put in the cell just
by clicking in the cell.
Is this possible.

P.S. The questionaire is more complex that just yes or no,
but this gives the gist of what I am tryin to achieve.
 
right click sheet tab>view code>copy/paste this>save workbook.
Works for row 4 on down

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 3 Then
If Target.Column = 2 Then Target = "Yes"
If Target.Column = 3 Then Target = "No"
End If
End Sub
 
BTW, you could even have this in the same column by using a single click for
no and a double click for yes.
 
Thanks so much Don,
You presumed my next question,
if I wanted to set the single click to the respnse = Yes
and the double click to "" or blank to compensate for user error
where is the single click or double click property.
I don't see it anywhere in the puldown for the porperties.

Again thanks for the quick response.
Brad
 
I found it...
I was looking for simply On Click or On Double Click
I'm used to using Access....
Anyway this should work nicely.
Thanks again Don
 
Don,

Am interested in this thread. What code would result in having "this
in the same column by using a single click for no and a double click
for yes"? I can't seem to figure it out? TIA

Mike
 
You could do something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then Target.Value = "No"
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 2 Then
Target.Value = "Yes"
Cancel = True
End If
End Sub


But I prefer the RightClick method:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 2 Then
Select Case Target.Value
Case "No": Target.Value = "Yes"
Case Else: Target.Value = "No"
End Select
Cancel = True
End If
End Sub

Rob
 
Back
Top