Inputbox with Listbox

J

Jeff

Hello,

I have a listbox of names with a row.source. I'd like, if possible, to
create a VBA macro that would, after clicking on a name, trigger an inputbox
that would enable me to add a $ value in the corresponding row (of the name
clicked) in Column E.
Jeff
 
N

Nick Hodge

Jeff

Made a few presumptions.

1) It's an activeX listbox on the worksheet (called ListBox1)
2) The 'listrange' (filling the list box' is in Range("A1:A11"))
3) This is only rough code. No error checking etc.
4) I am using the 'click' event of the control

Private Sub ListBox1_Click()
Dim sName As String
Dim dVal As Double
sName = ListBox1.Value
dVal = Application.InputBox("Enter a value for " & sName, , , , , , , 1)
Range("A1:A11").Find(sName).Offset(0, 1).Value = dVal
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
J

Jeff

Thank you Nick,
This is what I needed.
Could you please tell what should I do to make sure "dval" is added and does
not overwrite if there already a value the row ?
Thanks,
JF
 
N

Nick Hodge

Jeff

Private Sub ListBox1_Click()
Dim sName As String
Dim dVal As Double
sName = ListBox1.Value
dVal = Application.InputBox("Enter a value for " & sName, , , , , , , 1)
Range("A1:A11").Find(sName).Offset(0, 1).Value = _
Range("A1:A11").Find(sName).Offset(0, 1).Value + dVal
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top