Edit an Item in a Listbox

[SlinkRN]

Hi all, I can't seem to figure this one out by searching. It seems to be such a simple thing! I have a text box that fills a listbox with team names. After the user fills in a bunch of team names, I would like them to be able to click on one of the names in the listbox and edit it. I've tried this code but it is an infinite loop. Can anyone help me figure this out?
<code>
Private Sub lstTeams_Click()
Dim i As Integer
Dim sel As String
Dim chg As String
For i = 0 To ListBox1.ListCount - 1
If lstTeams.Selected(i) Then
sel = lstTeams.List(i)
chg = InputBox("Edit Item here", "Edit Name")
lstTeams.List(i) = chg
End If
Next i

End Sub
</code>

 

[Jaafar Tribak]

Is the Listbox on a worksheet or on a userform ?

 

[SlinkRN]

Is the Listbox on a worksheet or on a userform ?

It is in a userform named frmTeamNames2

 

[Fluff]

Whenever you change the value, it triggers the event again. Try

[COLOR=#ff0000]Dim DisableEvents As Boolean[/COLOR]
Private Sub ListBox1_Click()
   Dim i As Integer
   Dim sel As String
   Dim chg As String
   
   If DisableEvents Then Exit Sub
   DisableEvents = True
   For i = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(i) Then
         sel = ListBox1.List(i)
         chg = InputBox("Edit Item here", "Edit Name")
         ListBox1.List(i) = chg
      End If
   Next i
   DisableEvents = False
End Sub

The line in red must go at the very top of the module, before any code.
Change the Listbox name to suit

 

[SlinkRN]

Thanks so much Fluff, that does the trick!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[SlinkRN]

You're welcome & thanks for the feedback

After playing, I'm going to have to put in some sort of error check for when the user clicks "Cancel" on the input box. When I did that it erased the listbox item rather than leaving it as is.

 

[Fluff]

You can use

         If chg <> "" Then ListBox1.List(i) = chg

 

[SlinkRN]

Yes! Perfect Thanks again!

 

[Fluff]

My pleasure