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" <poppy.27jhin_1147176603.7325@excelforum-nospam.com> a écrit dans le
message de news: poppy.27jhin_1147176603.7325@excelforum-nospam.com...
>
> 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" <poppy.27jlga_1147181702.3694@excelforum-nospam.com> a écrit dans le
message de news: poppy.27jlga_1147181702.3694@excelforum-nospam.com...
>
> 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:


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

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" <poppy.27ou3z_1147426203.0779@excelforum-nospam.com> a écrit dans le
message de news: poppy.27ou3z_1147426203.0779@excelforum-nospam.com...
>
> 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
>

**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:

i = ListBox1.ListIndex
modelrow = range("A2:A1500").Find(what:=(ListBox1.List(i)), MatchCase:=True).Row
TypeCol = ActiveSheet.range("A2:ZZ2").Find(what:="Type", MatchCase:=True).Column
InfTypeValue = Application.index(Sheet3.range("A1:ZZ1500"), modelrow, TypeCol)
ListBox2.AddItem InfTypeValue

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

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

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

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?