Select & edit items in a listbox in VBA

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

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

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

Private Sub Texbox1_Change()
Dim rCell As Range
With ListBox1
Set rCell = Range(.List).Resize(1).Offset(.ListIndex)
rCell.Value = TextBox1.Value
End With
End Sub

Private Sub UserForm_Initialize()
 ListBox1.ColumnCount = 1
 ListBox1.List = [BHrange].Value
End Sub

Select & edit items in a listbox in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Select & edit items in a listbox in VBA

Re: Select & edit items in a listbox in VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions