Edit listbox item vba

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?

Works by me on all rows of ListBox1

See example: //cjoint.com/?fjrsTMoXcE

What did you type in RowSource ?

If it's a name, what does the name refer to ?

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