Hi there,

I have column P titled "Are controls for fraud risks working?" and I have
one next to it "give a description" I only need some narrative in there if
they select no on my dropdown for are the controls working column - is there
a really easy way of doing this with an input box?

Hi
I've read this a couple of times and still can't make sense of it. A
bit less haste in typing maybe?
regards
Paul


On Mar 14, 1:02 pm, Pasty wrote:
Hi there,

I have column P titled "Are controls for fraud risks working?" and I have
one next to it "give a description" I only need some narrative in there if
they select no on my dropdown for are the controls working column - is there
a really easy way of doing this with an input box?

Basically column p is a drop down box (yes/no) if they select yes then no
further action is required, if they select no they have to fill in a reason
in the cell next to it. Is this clearer?

" wrote:

Hi
I've read this a couple of times and still can't make sense of it. A
bit less haste in typing maybe?
regards
Paul


On Mar 14, 1:02 pm, Pasty wrote:
Hi there,

I have column P titled "Are controls for fraud risks working?" and I have
one next to it "give a description" I only need some narrative in there if
they select no on my dropdown for are the controls working column - is there
a really easy way of doing this with an input box?

Hi
Certainly is:

Put this in the code module behind your sheet that the dropdown is on
(in Editor, double click the sheet name)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If Target.Value = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

regards
Paul

This looks along the right lines but when I use it no input box pops up.

" wrote:

Hi
Certainly is:

Put this in the code module behind your sheet that the dropdown is on
(in Editor, double click the sheet name)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If Target.Value = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

regards
Paul

will Column P contain Lower case "n" and Lower case "o". If not, there's
your huckleberry. To account for that

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count 1 then exit sub
If Not Intersect(Target, Columns("P")) Is Nothing Then
If lcase(Target.Value) = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

If the above doesn't correct the problem, then have you put this in the
sheet module of the sheet with the data? Are events enabled?

--
Regards,
Tom Ogilvy


"Pasty" wrote:

This looks along the right lines but when I use it no input box pops up.

" wrote:

Hi
Certainly is:

Put this in the code module behind your sheet that the dropdown is on
(in Editor, double click the sheet name)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If Target.Value = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

regards
Paul

I changed the N and o to reflect whats in my drop down - whats this enable
events bit you speak of?

You'll have to forgive my ignorance its just I've decided to teach myself VBA!

"Tom Ogilvy" wrote:

will Column P contain Lower case "n" and Lower case "o". If not, there's
your huckleberry. To account for that

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count 1 then exit sub
If Not Intersect(Target, Columns("P")) Is Nothing Then
If lcase(Target.Value) = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

If the above doesn't correct the problem, then have you put this in the
sheet module of the sheet with the data? Are events enabled?

--
Regards,
Tom Ogilvy


"Pasty" wrote:

This looks along the right lines but when I use it no input box pops up.

" wrote:

Hi
Certainly is:

Put this in the code module behind your sheet that the dropdown is on
(in Editor, double click the sheet name)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("P")) Is Nothing Then
If Target.Value = "no" Then
Response = InputBox("Any Reason?")
Target.Offset(0, 1).Value = Response
End If
End If
End Sub

regards
Paul

Hi
This code needs to be in the correct place or it won't work. Open the
VB editor. In the project window (left hand side) you will see your
excel filename and under it various sheet names. Double click the
sheetname where you have your dropdown (I was assuming this was a
validation list to give you consistency of spelling yes/no). Now paste
Tom's code into the blank module.
I'm assuming, of course, that your dropdown really is in column P??
Also, I'm assuming you changed the value to no from yes. This will
fire the change event.
The events enabled bit just allows your sheet to respond to the change
in the cell value. You can switch this off using code but you probably
havn't done this so nothing to worry about there.
regards
Paul

Its okay I figured it out I just had to remove the LCase bit. Thanks for all
your help guys.

" wrote:

Hi
This code needs to be in the correct place or it won't work. Open the
VB editor. In the project window (left hand side) you will see your
excel filename and under it various sheet names. Double click the
sheetname where you have your dropdown (I was assuming this was a
validation list to give you consistency of spelling yes/no). Now paste
Tom's code into the blank module.
I'm assuming, of course, that your dropdown really is in column P??
Also, I'm assuming you changed the value to no from yes. This will
fire the change event.
The events enabled bit just allows your sheet to respond to the change
in the cell value. You can switch this off using code but you probably
havn't done this so nothing to worry about there.
regards
Paul

You didn't have to remove it (lcase) if you didn't change the no to No. You
need to do one or the other - not both.

Apparently you have a bad teacher <g

--
Regards,
Tom Ogilvy


"Pasty" wrote:

Its okay I figured it out I just had to remove the LCase bit. Thanks for all
your help guys.

" wrote:

Hi
This code needs to be in the correct place or it won't work. Open the
VB editor. In the project window (left hand side) you will see your
excel filename and under it various sheet names. Double click the
sheetname where you have your dropdown (I was assuming this was a
validation list to give you consistency of spelling yes/no). Now paste
Tom's code into the blank module.
I'm assuming, of course, that your dropdown really is in column P??
Also, I'm assuming you changed the value to no from yes. This will
fire the change event.
The events enabled bit just allows your sheet to respond to the change
in the cell value. You can switch this off using code but you probably
havn't done this so nothing to worry about there.
regards
Paul