Select & edit items in a listbox in VBA

  • Thread starter Thread starter poppy
  • Start date Start date
P

poppy

Hi All

I would like to know if it is possible to edit a list box directly?

I have a userform with a textbox to add items to a list box on th
form. I want to make it possible for the user to select an item fro
the list and either directly edit it or for the item to be placed i
the textbox for the user to edit. The actuall items in the list ar
stored in one of the sheets and I have defined a name for the range.

I set the value of the textbox = listbox1.value under the MouseDow
event of the listbox. However, I can't think how to update the edite
item back to the listbox. Basically I want to overwrite the old ite
with the new one.

I would appreciate any help.

Thanks

Kind Regard
 
Assuming the ListBox1 has RowSource=A1:A20

Paste the following in Userform's code:

HTH
--
AP

'--------------
Private Sub ListBox1_Click()
TextBox1.Value = ListBox1.Value
End Sub

Private Sub TextBox1_Change()
Dim rCell As Range
With ListBox1
Set rCell = Range(.RowSource).Offset(.ListIndex).Resize(1)
rCell.Value = TextBox1.Value
End With
End Sub
'------------------------
 
Hi Ardus

That piece of code your wrote is doing exactly what I want :) except
that it only works on the first item in the list :( . If I try
selecting a different item from the second one down, it throws a
run-time error 1004 - Application or Object defined error on this line:


Set rCell = Range(.RowSource).Offset(.ListIndex).Resize(1)

I set my rowsource to the name I defined for that list.

Could that be the problem?
 
Hi Ardus

This is what my code looks like:

Code:
--------------------


Private Sub ListBox1_Click()
txtRank.Value = ListBox1.Value
End Sub

Private Sub txtRank_Change()
Dim rCell As Range
With ListBox1
Set rCell = Range(.RowSource).Offset(.ListIndex).Resize(1)
rCell.Value = txtRank.Value
End With
End Sub


Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 1
ListBox1.RowSource = "SourceRank" 'I set it so that when the form is loaded the list in SourceRank(Defined name - Col A:A) is displayed in the listbox, I dont know if this might be the problem or not?

End Sub
 
That was because you define SourceRank as $A:$A
I don't think it's a good idea, since your ListBox shows 65536 rows!

Anyway, this fixes the bug:

Private Sub txtRank_Change()
Dim rCell As Range
With ListBox1
Set rCell = Range(.RowSource).Resize(1).Offset(.ListIndex)
rCell.Value = txtRank.Value
End With
End Sub

HTH
 
Hi Ardus

Thank you. My listbox works perfectly now. I really appreciate all your
help and patience.

Kind Regards
 
Back
Top