VBA question involving an InputBox

[Don Lindros]

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

 

[Vasant Nanavati]

Not sure what you mean by "the end of that row." Perhaps:

Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(, 1) =
varUserInput

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim varUserInput As Variant

With Target(1)
If Not Intersect(.Cells, Range("K2:S212")) Is Nothing Then
If .Value = "FAIL" Then
Do
varUserInput = Application.InputBox( _
prompt:="Enter comments: ", _
Title:="Comment Field", _
Type:=2)
If varUserInput = False Then Exit Sub 'cancelled
Loop Until Len(varUserInput) > 0
Application.EnableEvents = False
Cells(.Row, Columns.Count).End(xlToLeft).Offset( _
0, 1).Value = varUserInput
'or perhaps
'Cells(.Row, 20).Value = varUserInput
Application.EnableEvents = True
End If
End If
End With
End Sub

Wasn't sure what "end of the current row" meant - the code above puts
the comment after the last filled cell in the row. The commented code
puts it in column T.

 

[Bob Phillips]

Hi Don,

If varUserInput <> "" Then
me.Cells(target.row,"Z").Value = varUsereIn
Endif

will put the result in column Z

If you want to find the last cell of data use

If varUserInput <> "" Then
me.Cells(target.row,
Cells(target.row,columns.count).end(xlToLeft)+1).Value = _
varUsereIn
Endif

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)