I was wondering if anyone knew the syntax for this:

I have a worksheet macro that senses when a cell value is "FAIL". It
then prompts the user with an input box, and sends that result to
another cell at the end of that row. I'm having trouble with writing
the result of the input box to another cell.

Can this be done with ranges...? I've yet to get the syntax right.

Here's what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("K2:S212")) Is Nothing Then
If Target.Value = "FAIL" Then
Dim varUserInput As Variant
varUserInput = InputBox("Enter comments: ", _
"Comment Field", "")
If varUserInput < "" Then

'here's where I need to write varUserInput to a cell at
' the end of the current row



End If
End If
End If
End Sub

You don't say what you mean by the "end of the row", so
I'll assume that since you're checking range K2:S2 that
the reply goes in column T

cells(Target.row,"T").Value = varUserInput

Note that this will fire the change event once more, but
as T is out of the range, the input box isn't used!

TIP:
One could always turn off events for this type of
functionality if required
eg

Application.EnableEvents = False
cells(target.row,"T").Value = varUserInput
Application.EnableEvents = True


This isn't really necessary for the question as defined
though it's worth keeoping in mind


Patrick Molloy
Microsoft Excel MVP
-------------------
I Feel Great

-----Original Message-----
I was wondering if anyone knew the syntax for this:

I have a worksheet macro that senses when a cell value
is "FAIL". It
then prompts the user with an input box, and sends that
result to
another cell at the end of that row. I'm having trouble
with writing
the result of the input box to another cell.

Can this be done with ranges...? I've yet to get the
syntax right.

Here's what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("K2:S212"))
Is Nothing Then
If Target.Value = "FAIL" Then
Dim varUserInput As Variant
varUserInput = InputBox("Enter comments: ", _
"Comment Field", "")
If varUserInput < "" Then

'here's where I need to write varUserInput to a
cell at
' the end of the current row



End If
End If
End If
End Sub
.