Deleting in a list box
 

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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

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


"Bob Phillips" wrote:

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

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)

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


"Bob Phillips" wrote:

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

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.
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Bob Phillips" wrote:

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)

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


"Bob Phillips" wrote:

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

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.
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Bob Phillips" wrote:

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)

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


"Bob Phillips" wrote:

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Deleting in a list box
 

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

Range("B2:B100")(lstData.ListIndex).Entirerow.Dele te

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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.
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Bob Phillips" wrote:

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)

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


"Bob Phillips" wrote:

To delete the selected item, just use

lstData.RemoveItem (lstData.ListIndex)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer