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

--
popp
-----------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...fo&userid=1145
View this thread: http://www.excelforum.com/showthread.php?threadid=54022

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
'------------------------
"poppy" a écrit dans le
message de news: ...

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 the
form. I want to make it possible for the user to select an item from
the list and either directly edit it or for the item to be placed in
the textbox for the user to edit. The actuall items in the list are
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 MouseDown
event of the listbox. However, I can't think how to update the edited
item back to the listbox. Basically I want to overwrite the old item
with the new one.

I would appreciate any help.

Thanks

Kind Regards


--
poppy
------------------------------------------------------------------------
poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220

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?


--
poppy
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220

Works by me on all rows of ListBox1

See example: http://cjoint.com/?fjrsTMoXcE

What did you type in RowSource ?

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

--
AP

"poppy" a écrit dans le
message de news: ...

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?


--
poppy
------------------------------------------------------------------------
poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220

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

--------------------


Kind Regards


--
poppy
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220

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
--
AP

"poppy" a écrit dans le
message de news: ...

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

--------------------


Kind Regards


--
poppy
------------------------------------------------------------------------
poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220

Hi Ardus

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

Kind Regards


--
poppy
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=540220