Styjun

I'm worikng on a project for my senior year project and I have to deal with the Activex controls for my userinterface. For deleting the single item in the listbox, I have written the code below.

When I debug the code, I'm having an "automation error 404" at the last part of my code:

With Worksheets("Solve").OLEObjects("ListBox1")
 .ListFillRange = .ListFillRange
End With

Here is my code:

Dim i As Integer
Dim lastrow As Integer

lastrow = Sheets("SrData").Cells(Sheets("SrData").Rows.Count, "A").End(xlUp).Row


If MsgBox("Are you sure you want to delete this Sales Representative?", vbYesNo + vbQuestion, "Delete Sales Representative") = vbYes Then

 For i = 2 To lastrow


 sheetcontrol = Sheets("SrData").Cells(i, 1)
 listcontrol = Sheets("Solve").ListBox1.List(Sheets("Solve").ListBox1.ListIndex)

 If sheetcontrol = listcontrol Then

 Sheets("SrData").Activate
 ActiveWorkbook.Names("SrDat").Delete

 Sheets("SrData").Rows(i).Select
 Selection.Delete

 Sheets("SrData").Range(Sheets("SrData").Cells(2, 1), Sheets("SrData").Cells(lastrow - 1, 3)).Select

 ActiveWorkbook.Names.Add Name:="SrDat", RefersTo:=Selection



 End If

 Next i

End If


With Worksheets("Solve").OLEObjects("ListBox1")
 .ListFillRange = .ListFillRange
End With

I would try working with something like this:

For i = ListBox1.ListCount - 1 To 0 Step -1
 If ListBox1.Selected(i) Then
 ListBox1.RemoveItem i
 End If
 Next i

Edit: then you can delete the row at your “Solve” sheet with your code and update listrange like this:

Private sub userform_initialize()
With Worksheets("Solve").Range("A2:A" & lastrow) '<-- change with your range
 Me.ListBox1.List = Application.Transpose(.Cells)
 Set listRng = .Cells
 End With 
End Sub

I have found an solution for my problem here is my edited code.

Set s = Worksheets("Solve")
Set sd = Worksheets("SrData")
Dim lastrow As Integer
Dim target As String
Dim sheetcontrol As String

Application.ScreenUpdating = False

target = s.ListBox1.List(s.ListBox1.ListIndex)
lastrow = sd.Cells(sd.Rows.Count, "A").End(xlUp).Row

If IsNull(ListBox1) Then

 MsgBox "You haven't selected an Sales Representative. Please Select one."

Else

 If MsgBox("Are you sure you want to delete this Sales Representative?", vbYesNo + vbQuestion, "Delete Sales Representative") = vbYes Then

 For i = 2 To lastrow
 sheetcontrol = sd.Cells(i, 1)

 If sheetcontrol = target Then

 s.ListBox1.ListIndex = -1

 sd.Rows(i).Delete
 sd.Activate
 ActiveWorkbook.Names("SrDat").Delete
 Range("'SrData'!$A$2:$C$" & lastrow - 1).Name = "SrDat"
 s.Activate
 End If
 Next i
 End If

End If
refSr
Application.ScreenUpdating = True

in refSr part I'm doing the

 With Worksheets("Solve").OLEObjects("ListBox1")
 .ListFillRange = .ListFillRange
 End With