I have a spreadsheet in the form of a Customer Satisfaction Survey.


Where F7 is Are you happy with the speed of our servise .. with a
Yes/No drop down box
where G7 is Are you satisfied with the attitude of our staff .. with a
Yes/No drop down box
and H7 is Are you pleased with the accuracy of our work.. Yes/No
and J7 is for customer comments


So if we have 3 "Yes"s, we do not want extra comments unless the
customer wishes to go to J7, however if we have a "No", then we would
like to know why. So after they have sellected H7, then I want Excel to
review the row and if there is one or more "No"s then I want to take
the customer to J7 so they can comment as to why.


I did not want it to go to J7 after each question, incase the customer
then never went back to the remaining questions.

So in its simplist form I am trying to say

If on Exit from H7, F7 or G7 or H7 = No then go to J7 else go to F8

Any suggestions greatfully received.

with VBA


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If LCase(Range("F7").Value) = "no" Or _
LCase(Range("G7").Value) = "no" Or _
LCase(Range("H7").Value) = "no" Then
Range("J7").Activate
Else
Range("F8").Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJG" wrote in message
oups.com...
I have a spreadsheet in the form of a Customer Satisfaction Survey.


Where F7 is Are you happy with the speed of our servise .. with a
Yes/No drop down box
where G7 is Are you satisfied with the attitude of our staff .. with a
Yes/No drop down box
and H7 is Are you pleased with the accuracy of our work.. Yes/No
and J7 is for customer comments


So if we have 3 "Yes"s, we do not want extra comments unless the
customer wishes to go to J7, however if we have a "No", then we would
like to know why. So after they have sellected H7, then I want Excel to
review the row and if there is one or more "No"s then I want to take
the customer to J7 so they can comment as to why.


I did not want it to go to J7 after each question, incase the customer
then never went back to the remaining questions.

So in its simplist form I am trying to say

If on Exit from H7, F7 or G7 or H7 = No then go to J7 else go to F8

Any suggestions greatfully received.

Bob,
Thank you, I have copy and pasted as suggested but nothing at all
seems to happen. I have tried typing "no" in F7-H7 (i have tried
several combinations no, no, yes // yes no yes etc.
Is there anything I need to do to activate the code.


Bob Phillips wrote:
with VBA


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If LCase(Range("F7").Value) = "no" Or _
LCase(Range("G7").Value) = "no" Or _
LCase(Range("H7").Value) = "no" Then
Range("J7").Activate
Else
Range("F8").Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJG" wrote in message
oups.com...
I have a spreadsheet in the form of a Customer Satisfaction Survey.


Where F7 is Are you happy with the speed of our servise .. with a
Yes/No drop down box
where G7 is Are you satisfied with the attitude of our staff .. with a
Yes/No drop down box
and H7 is Are you pleased with the accuracy of our work.. Yes/No
and J7 is for customer comments


So if we have 3 "Yes"s, we do not want extra comments unless the
customer wishes to go to J7, however if we have a "No", then we would
like to know why. So after they have sellected H7, then I want Excel to
review the row and if there is one or more "No"s then I want to take
the customer to J7 so they can comment as to why.


I did not want it to go to J7 after each question, incase the customer
then never went back to the remaining questions.

So in its simplist form I am trying to say

If on Exit from H7, F7 or G7 or H7 = No then go to J7 else go to F8

Any suggestions greatfully received.

A couple of important bits missing. Try this alternative

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H7"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If LCase(Range("F7").Value) = "no" Or _
LCase(Range("G7").Value) = "no" Or _
LCase(Range("H7").Value) = "no" Then
Range("J7").Activate
Else
Range("F8").Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

put it in the same place.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJG" wrote in message
oups.com...
Bob,
Thank you, I have copy and pasted as suggested but nothing at all
seems to happen. I have tried typing "no" in F7-H7 (i have tried
several combinations no, no, yes // yes no yes etc.
Is there anything I need to do to activate the code.


Bob Phillips wrote:
with VBA


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If LCase(Range("F7").Value) = "no" Or _
LCase(Range("G7").Value) = "no" Or _
LCase(Range("H7").Value) = "no" Then
Range("J7").Activate
Else
Range("F8").Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJG" wrote in message
oups.com...
I have a spreadsheet in the form of a Customer Satisfaction Survey.


Where F7 is Are you happy with the speed of our servise .. with a
Yes/No drop down box
where G7 is Are you satisfied with the attitude of our staff .. with
a
Yes/No drop down box
and H7 is Are you pleased with the accuracy of our work.. Yes/No
and J7 is for customer comments


So if we have 3 "Yes"s, we do not want extra comments unless the
customer wishes to go to J7, however if we have a "No", then we would
like to know why. So after they have sellected H7, then I want Excel
to
review the row and if there is one or more "No"s then I want to take
the customer to J7 so they can comment as to why.


I did not want it to go to J7 after each question, incase the customer
then never went back to the remaining questions.

So in its simplist form I am trying to say

If on Exit from H7, F7 or G7 or H7 = No then go to J7 else go to F8

Any suggestions greatfully received.

Bob,
Sorry, same as before.. nothing happens after putting "no" in
one or all of F7-H7. Is there anything else I need to do after copy &
pasting into the worksheet code module.

The code is based upon inputting data in H7, then it checks the 3 cells to
see if any have a 'no'.

Did you store it in the worksheet code module as shown in my original reply?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJG" wrote in message
oups.com...
Bob,
Sorry, same as before.. nothing happens after putting "no" in
one or all of F7-H7. Is there anything else I need to do after copy &
pasting into the worksheet code module.