Simple listbox search with a msgbox (VBA)-CodePudding

This simple listbox search doesn't seem to work, and I have no idea why.

Private Sub TesteButton_Click()

Dim s As Integer

Me.ListBox1.AddItem "Peter"
Me.ListBox1.AddItem "Alex"
Me.ListBox1.AddItem "Gustav"

For s = 0 To Me.ListBox1.ListCount - 1
    If "Gustav" = Me.ListBox1.List(s) Then
        MsgBox "Exist"
    Else
        MsgBox "Does not exist"
    End If
Exit For
Next
End Sub

CodePudding user response：

In reality what I need is the ability do check if exist, and if not, create. But I can use Toddleson code for that I suppose.

Private Sub TesteButton_Click()

Dim s As Integer

Me.ListBox1.AddItem "Peter"
Me.ListBox1.AddItem "Alex"
Me.ListBox1.AddItem "Gustav"


For s = 0 To Me.ListBox1.ListCount - 1
    If Not "Mary" = Me.ListBox1.List(s) Then
        Me.ListBox1.AddItem "Mary"
    End If
Exit For
Next
End Sub

Thank you very much!

CodePudding user response：

The placement of your Exit For was causing the For loop to exit on the first pass. This is my suggestion of a corrected structure:

Private Sub TesteButton_Click()

    Dim s As Integer
    
    Me.ListBox1.AddItem "Peter"
    Me.ListBox1.AddItem "Alex"
    Me.ListBox1.AddItem "Gustav"
    
    Dim isFound As Boolean
    For s = 0 To Me.ListBox1.ListCount - 1
        If "Gustav" = Me.ListBox1.List(s) Then
            isFound = True
            Exit For
        End If
    Next
    
    If isFound Then
        MsgBox "Exist"
    Else
        MsgBox "Does not exist"
    End If

End Sub

If for example, you wanted to add an item to the list only if it wasn't already in the list you can do:

Private Sub Example()
    AddToList Me.ListBox1, "Peter"
    AddToList Me.ListBox1, "Alex"
    AddToList Me.ListBox1, "Gustav"
    AddToList Me.ListBox1, "Gustav" 'will be skipped, since its not unique
    AddToList Me.ListBox1, "Mary"
End Sub

Private Sub AddToList(ToListBox As Object, NewItem As String)
    Dim s As Integer
    Dim isFound As Boolean
    
    For s = 0 To ToListBox.ListCount - 1
        If NewItem = ToListBox.List(s) Then
            isFound = True
            Exit For
        End If
    Next
    
    If Not isFound Then ToListBox.AddItem NewItem
End Sub