Move to a set cell if

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" <bob@mystical.demon.co.uk> wrote in message
news:1129497797.958535.140850@g49g2000cwa.googlegroups.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" <bob@mystical.demon.co.uk> wrote in message
> news:1129497797.958535.140850@g49g2000cwa.googlegroups.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" <bob@mystical.demon.co.uk> wrote in message
news:1129557264.978751.106210@g43g2000cwa.googlegroups.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" <bob@mystical.demon.co.uk> wrote in message
> > news:1129497797.958535.140850@g49g2000cwa.googlegroups.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" <bob@mystical.demon.co.uk> wrote in message
news:1129560394.250024.189300@f14g2000cwb.googlegroups.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.
>

Bob,
My fault, I needed to delete the yes/no's that I had input to test
the previous code. Thank you for your time.

Bob,
Thank you, works as asked.

Can the code also be used to loop down twenty rows? We actually
have that many questions to ask one under the other, or is the best
option to copy and paste your original code twentry times but just
change the row numbers each time.

Just change this bit

Const WS_RANGE As String = "H7"

to

Const WS_RANGE As String = "H7:H27"

--

HTH

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


"RJG" <bob@mystical.demon.co.uk> wrote in message
news:1129564743.834201.261890@f14g2000cwb.googlegroups.com...
> Bob,
> Thank you, works as asked.
>
> Can the code also be used to loop down twenty rows? We actually
> have that many questions to ask one under the other, or is the best
> option to copy and paste your original code twentry times but just
> change the row numbers each time.
>

Bob,
I realise this must be starting to turn into a bad dream, I have
tried your suggestion but it returns the curser to the same point. (J7)
but in reality I would like it to go to the row in question (J7:J27). I
have tried "playing" with the code without success. Is there an easy
fix.??

Try it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H7:H27"
Dim TRow As Integer

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
TRow = Target.Row
With Target
If LCase(Cells(TRow, 6).Value) = "no" Or _
LCase(Cells(TRow, 7).Value) = "no" Or _
LCase(Cells(TRow, 8).Value) = "no" Then
Cells(TRow, 10).Activate
Else
Cells(TRow + 1, 6).Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Hope this helps
Rowan

RJG wrote:
> Bob,
> I realise this must be starting to turn into a bad dream, I have
> tried your suggestion but it returns the curser to the same point. (J7)
> but in reality I would like it to go to the row in question (J7:J27). I
> have tried "playing" with the code without success. Is there an easy
> fix.??
>