Edit an Item in a Listbox

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
697
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>
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,842
Office Version
2016
Platform
Windows
Is the Listbox on a worksheet or on a userform ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Whenever you change the value, it triggers the event again. Try
Code:
[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

Well-known Member
Joined
Oct 29, 2002
Messages
697
Thanks so much Fluff, that does the trick!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
697
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
You can use
Code:
         If chg <> "" Then ListBox1.List(i) = chg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
My pleasure
 

Watch MrExcel Video

Forum statistics

Threads
1,099,143
Messages
5,466,931
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top