Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refresh

luzmen · Jul 26th 2017

Hi everyone again,

I have this code to display in the listbox and update it if necessary. Now, I added a command button with a code to delete the selected item in the ListBox, but there is a "Compile Error: Method or data member not found".

Please note that the source of the listbox with a range name "FilterFR1st" is from other sheets which filtered from the raw source sheet. This is due to filtering for specific ID. Raw source worksheets name is "FR1st".

Code where Compile Error appears

Code

ListBox1.ListFillRange = "a:a"

Code for command button "Delete"

Code

Private Sub DelFR1st_Click()
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 1
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete
            ListBox1.ListFillRange = "a:a"
        End If
    Else
    End If
End Sub

Display More

Whole code of my UserForm:

Code

Option Explicit
Dim rData
Dim oCtl As MSForms.Control
Dim iX As Integer


Private Sub SaveFR1st_Click()
   
    With Me
        For Each oCtl In .Controls
            If TypeName(oCtl) = "TextBox" And IsNumeric(oCtl.Tag) Then
                iX = oCtl.Tag
                Sheets("FR1st").Cells(Me.ListBox1.ListIndex + 2, iX) = oCtl.Value
                Sheets("FR1st").Cells(Me.ListBox1.ListIndex + 2, 6) = TextBox34.Value
            End If
        Next oCtl
    End With


    LoadData
End Sub




Private Sub ListBox1_Click()


    With Me
        For Each oCtl In .Controls
            If TypeName(oCtl) = "TextBox" And IsNumeric(oCtl.Tag) Then
                iX = oCtl.Tag
                oCtl.Value = .ListBox1.List(.ListBox1.ListIndex, iX - 1)
            End If
        Next oCtl
    End With
End Sub




Private Sub UserForm_Initialize()
    LoadData
End Sub




Sub LoadData()


    Set rData = [FilterFR1st]
    TextBox34.Value = Format(Date, "mm/dd/yyyy")
    With Me.ListBox1


        .ColumnCount = 6
        .ColumnWidths = "0;83;100;95;90;0"
        .List = rData.Value
    End With
End Sub




Private Sub CloseFR1st_Click()
    Unload Me
End Sub




Private Sub DelFR1st_Click()
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 1
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete
            ListBox1.ListFillRange = "a:a"
        End If
    Else
    End If
End Sub

Display More

Hope i explain it clearly...

What is the code to delete the entire row from raw source sheet with the selected item in the ListBox? I want also an Auto Refresh from the ListBox after deleting the record..

Thank you in advance.

Max1616 · Jul 27th 2017

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Have you tried putting the sheet name where the listbox lives in front of the listbox?

Sheets("ListBoxSheetName").ListBox1.ListFillRange = "a:a"

luzmen · Jul 28th 2017

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Thanks Max161 for your reply... i tried what you say, still compile error appears and delete the row before the exact items.

However, i had manage to correct the code and now its working properly.

Code

Private Sub DelFR1st_Click()    
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 2
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete


        End If
        LoadData
    Else
    End If
End Sub

Display More

luzmen · Jul 28th 2017

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Can someone explain me why it will delete the row before the selected item/row with this code?

Code

indexi = ListBox1.ListIndex + 1

Max1616 · Jul 28th 2017

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Hi Luzmen,

This is because a listbox index starts at 0. So if you have a list that corresponded to rows 1-10. When the item in row 10 is selected, the list box index is 9 (full list index is 0-9).

My guess is your index starts in row 2, so in this case row 2 = listbox.listindexindex 0

Lets use a hypothetical:

If your listbox contains values starting in row 2 to row 15 (2:15), your index for this list looks like this:
0 = Row 2
1 = Row 3
2 = Row 4
3 = Row 5
etc.
.
.
11 = Row 15

So if you wanted to delete the row 10, you would need to set your list box to the row 10 value (Which in this case is index then use Rows(ListBox.ListIndex + 2) since your index and row values are two digits off.

I hope this makes sense and answers your question!

Sincerely,
Max

luzmen · Jul 29th 2017

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Thanks Max, its explain well..

Now, i encountered a problem... it well also delete row with other ID#..

Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refresh

Participate now!

Share