VBA, Deselect Items in the ListBox which are first forced to be selected

Question

Following is the issue: 1) I force some Items to be selected in a ListBox over VBA Code 2) Open UserForm and select or deselect some Items. (everything seems fine in the ListBox) 3) Write the selected Items out. If i only select some new items, everyhing works fine. If i deselect a selected item, which i forced at the beginning to be selected, it is still selected in the output.

With Sheets("ID_Mitarbeiter").Range("A2:A1048576")
 Set c = .Find(What:=TextBox_ID, lookat:=xlWhole)
 If Not c Is Nothing Then
    firstAddress = c.Address
    Do
        ListBox_Mitarbeiter.Selected(Sheets("ID_Mitarbeiter").Cells(c.Row, 2) - 1) = True
        Set c = .FindNext(c)
    If c Is Nothing Then
        GoTo DoneFinding
    End If
    Loop While c.Address <> firstAddress
  End If

DoneFinding: End With

Kindly clarify the problem/error encountered. There seems no code provided for deselecting or writing output. It seems B column values for match found of TextBox_ID is used to select the listbox items. — Ahmed AU

Egli Matthias Egli Matthias · Accepted Answer · 2018-11-06T08:11:05

Private Sub Butto_Change_Click()
Dim ind_pers As Integer

With ListBox_Mitarbeiter
If .ListCount > 0 Then
    ind_pers = 0
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
        Cells(ind_pers, 1).Value = 1
        Cells(ind_pers, 2).Value = i + 1
        ind_pers = ind_pers + 1
        Else
        End If
    Next i
    Else
End If
End With

End Sub

Private Sub ComboBox1_Change()

TextBox_IndexStart.Value = Sheets("ID_Mitarbeiter").Range("A2:A1048576").Find(What:=TextBox_ID.Value, lookat:=xlWhole).Row

With Sheets("ID_Mitarbeiter").Range("A2:A1048576")
     Set c = .Find(What:=TextBox_ID, lookat:=xlWhole)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            ListBox_Mitarbeiter.Selected(Sheets("ID_Mitarbeiter").Cells(c.Row, 2) - 1) = True
            Set c = .FindNext(c)
        If c Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
DoneFinding:
End With

End If
End Sub


Private Sub UserForm_Initialize()
Dim last As Integer
Dim cnt As Integer

last = Sheets("Mitarbeiter").Cells(Rows.Count, 1).End(xlUp).Row

For cnt = 2 To last
    With ListBox_Mitarbeiter
    .AddItem Sheets("Mitarbeiter").Cells(cnt, 2).Value & " " & Sheets("Mitarbeiter").Cells(cnt, 3).Value
    End With
Next cnt
End Sub

This code works fine. But when I, in the User Interface, deselect the items, it still writes out all the selected items in the first place.

VBA, Deselect Items in the ListBox which are first forced to be selected

1 Answers