ListBox Compile Error

[ipbr21054]

Hi,
I dont normaly use Listbox but trying it today.

I can load listbox fine but i get a compile error when i select an item to the n delete a record.
The code below in red is my issue but not sure why & also assume once that is sorted the rest is ok

Private Sub ListBox1_Click()
For i = Range "GRASS!A5:B" & Range("B" & Rows.Count).End(xlUp).Row
   If listDisplay.Selected(i) Then
        Rows(i).Delete
    End If
End Sub

 

[DanteAmor]

It's hard to understand what you want to do just by putting the error.

You must explain what you need to do.

• Do you want to delete a record in the sheet?
• Do you want to delete the record in the listbox?
• Is the listbox in the sheet or in a userform?
• How did you load the data into the listbox?

You see?
There are more doubts than solutions to your problem.

I'm going on an adventure with this:
If you are going to
- delete the listbox record,
- in a userform,
- with the data loaded into the listbox with the Additem method,
- when you select it with a click, then try this:

'This is a global variable declaration, it must go at the beginning of all code.
Dim deleting As Boolean

Private Sub ListBox1_Click()
  If deleting = True Then Exit Sub
  deleting = True
  With ListBox1
    .RemoveItem (.ListIndex)
  End With
  deleting = False
End Sub

 

[ipbr21054]

Hi,Best i show what i have at present.

The information from the worksheet is loaded intop the userform listbox.
The user makes a selection in the listbox example "TOM JONES"
The record on the worksheet for TOM JONES should now be deleted.

Option Explicit
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

Private Sub ListBox1_Click()
For i = Range "GRASS!A5:B" & Range("B" & Rows.Count).End(xlUp).Row
   If listDisplay.Selected(i) Then
        Rows(i).Delete
    End If
End Sub

Private Sub UserForm_Initialize()
ListBox1.RowSource = "GRASS!A5:B" & Range("B" & Rows.Count).End(xlUp).Row
End Sub

 

[ipbr21054]

Yes delete the record from the worksheet.
Yes delete the record from the listbox.
The listbox is on a userform.

 

[DanteAmor]

To delete a record by clicking, the following is enough, the detail is that the following is selected.

Option Explicit

Dim deleting As Boolean

Private Sub ListBox1_Click()
  If deleting = True Then Exit Sub
  deleting = True
  Sheets("GRASS").Rows(ListBox1.ListIndex + 5).Delete
  deleting = False
End Sub

Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("GRASS").Range("B" & Rows.Count).End(xlUp).Row
  If lr > 4 Then ListBox1.RowSource = "GRASS!A5:B" & lr
End Sub

Private Sub CommandButton1_Click()
  Unload UserForm1
End Sub

I recommend that you use the double click event, in this way the next item will not be selected. So to delete an item you must press double click.

Option Explicit

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim i As Long
  Dim lr As Long
  Dim sh As Worksheet

  Set sh = Sheets("GRASS")
  With ListBox1
    sh.Rows(.ListIndex + 5).Delete
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    If lr > 4 Then .RowSource = sh.Name & "!A5:B" & lr
    For i = 0 To .ListCount - 1
      If .Selected(i) Then .Selected(i) = False
    Next
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("GRASS").Range("B" & Rows.Count).End(xlUp).Row
  If lr > 4 Then ListBox1.RowSource = "GRASS!A5:B" & lr
End Sub

Private Sub CommandButton1_Click()
  Unload UserForm1
End Sub

Try the 2 codes and see which one solves what you need.

 

[ipbr21054]

Thanks very much.
I decided to use the Doublce Click option