Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Deleting a List from Another (Larger) List | Excel Discussion (Misc queries) | |||
List deleting or changing | New Users to Excel | |||
Deleting a drop-down list | Excel Discussion (Misc queries) | |||
Deleting data in a list | Excel Discussion (Misc queries) | |||
Deleting rows from list of files | Excel Programming |