Deleting in a list box

[Guest]

Well it's been awhile. This stuff can just take over your life that when i
lay off for a bit it ends up being a long spell. Well this should be easy.
I have a userform with a list box containing 8 columns when i click on a row
in the list box it fills the labels. I would like the option of deleting the
entire row they see fit. I attached my update button just to give you an idea
of what is going on. Thank you. Jennifer

Private Sub btnUpdate_Click()
Dim pointer As String
pointer = lstData.ListIndex
If pointer = -1 Then Exit Sub


For index = 2 To source.Rows.Count
If source.Cells(index, 1) = txtDataID.Text Then

With source
.Cells(index, eBoxes.Vendor) = txtVendor.Text
.Cells(index, eBoxes.MiscCosts) = txtMiscCosts.Text
.Cells(index, eBoxes.ProduceID) = Trim(txtProduceID.Text)

.Cells(index, eBoxes.PalletNum) = txtPallet.Text
.Cells(index, eBoxes.QtyPurchased) = txtQty.Text
.Cells(index, eBoxes.QtySold) = txtSold.Text
.Cells(index, eBoxes.Price) = txtPrice.Text
.Cells(index, eBoxes.Frt) = txtFrt.Text
End With

Exit For

End If
Next

LoadData
lstData.ListIndex = pointer

End Sub

 

[Bob Phillips]

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thank you that works but I also need it to delete the entire row in the
database.
--
Though daily learning, I LOVE EXCEL!
Jennifer

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Then you need to use the Listindex as an index into the database range.

BTW if the listbox is bound to the range, just delete the row in the range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Thank you that works but I also need it to delete the entire row in the
database.

 

[Guest]

Hey Bob,
I tried this:

Private Sub cmdDelete_Click()
Dim RangeDelete As Range

Set RangeDelete = Range(lstData.ListIndex)

RangeDelete.Delete Shift:=xlShiftUp
End Sub

Had no luck and am getting the error "Method 'Range" of object failed Globel"

Sorry you are going to have to give me a little more help, it has been awhile.

 

[Guest]

I don't know if it helps to know that the list box is in a user form that is
pulling its data from the database. Mirror reflection if you will. Thank you
so much I have to get this figured out.

 

[Bob Phillips]

Assuming your range is say B2:B100, you would use something like

Range("B2:B100")(lstData.ListIndex).Entirerow.Delete

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)