VBA / UserForm / DeleteButton

[maxblack]

Hello Guys,

I need your support with one thing related to Userfrom, which I'm preparing. I must to say that I'm pretty fresh in userforms, I'm still learning this part of VBA.
Anyway, I have a listbox with accounts, which should be deleted from the main report. I'm preparing the DeleteButton, which will be deleting account or accounts (possible multiple selection) from this list, which is in sheet("Setup"). i have already dony AddButton, which is working correcctly, but i have the problem with Delete.

I build following code:

Private Sub DeleteButton_Click()

Set wb = ThisWorkbook

Dim i As Integer

For i = 0 To ListBox1.ListCount - 1
     If ListBox1.Selected(i) Then
        'ListBox1.RemoveItem i
        Sheets("Setup").Rows(i + 1).EntireRow.Delete
     End If
Next i

Unload Me
LoadList

End Sub

If I select single account it works, but if I select multiple it delete values not properly.
I'm sure that I made a mistake in this loop.

But I have a new idea, but I don't know if it is possible with userfrom.
Is is possible to add multi selected values to AutoFilt Array?
i would be great if macro will be selecting this way the account and delete the whole selection?

Thanks for you support!
max

 

[Norie]

max

Reverse the loop.

For i =ListBox1.ListCount - 1 To 0 Step -1
     If ListBox1.Selected(i) Then
        'ListBox1.RemoveItem i
        Sheets("Setup").Rows(i + 1).EntireRow.Delete
     End If
Next i

 

[maxblack]

Amazing, simply but amazing

What about connecting Listbox selection with AutoFiltr - is it possible?

 

[Norie]

You could be able to take the selected values from the listbox, put them in an array and use them in an autofilter.

The first part should be easy, the second not so - using arrays in autofilter can be a bit tricky.

 

[maxblack]

Maybe could you give me some hints? Or maybe you know a good place where I could read about it, because I'm very "fresh" in using userforms :D

I was looking into forum to solve my problem and I found something like this:

[COLOR=#333333]Option Explicit[/COLOR]
Private Sub CommandButton1_Click()

    Dim MyArray() As String
    Dim Cnt As Long
    Dim r As Long
    
    Cnt = 0
    With Me.ListBox1
        If .ListIndex <> -1 Then
            For r = 0 To .ListCount - 1
                If .Selected(r) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = .List(r)
                End If
            Next r
        End If
    End With
        
  With Worksheets("Setup")
        If .FilterMode Then .ShowAllData
        .Range("A1:R1").AutoFilter field:=17, Criteria1:=MyArray, Operator:=xlFilterValues
  End With
   [COLOR=#333333]End Sub[/COLOR]

But i have a problem with understanding how it works and not sure if its ok.

 

[Norie]

Does that code work?