Select & edit items in a listbox in VBA

**poppy** · 05-09-2006, 08:06 AM

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

Ardus Petus · 05-09-2006, 08:55 AM

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" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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
>

**poppy** · 05-09-2006, 09:33 AM

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?

Ardus Petus · 05-09-2006, 11:25 AM

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" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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
>

**poppy** · 05-12-2006, 05:26 AM

Hi Ardus

This is what my code looks like:

Please Login or Register  to view this content.

Kind Regards

Ardus Petus · 05-12-2006, 06:10 AM

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" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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
>

**poppy** · 05-12-2006, 08:10 AM

Hi Ardus

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

Kind Regards

**bhsoundman** · 12-14-2012, 02:08 AM

So I'm trying this same code & it works for me, all the way to executing the changes from textbox back into the cell. Ay ideas? BTW, I'm on Excel 2011 for Mac & there were a few changes needed to keep the code working to that point. Here's my code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Feloni** · 01-31-2013, 04:48 PM

HELLo all

i'm kinda newb in VB and i tried using the codes above (adapting them of course) to enable editing called cells from the textbox, but i had no luck at all

i was thinking that maybe it was because of the way i called the info into the listbox

each line of the listbox was added like this:

Please Login or Register  to view this content.

this is how've i've been adding so far, then i added this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but it throws me error in this line:

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

what should i do?

**arlu1201** · 02-01-2013, 01:14 AM

Feloni,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

Also,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**rbyrd023** · 04-04-2014, 04:47 PM

I know that this is a old thread, but I am trying to do something similar. The only difference is that my listbox contains five columns. I only want to change the first column (column 0), and the code above changes all five to what is in the textbox. Any way around this?