Deleting listbox list, from listbox and source

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I would like to double click on one of the items / rows of data in the list box and delete that item in the list box as well as the source in the spreadsheet. this is multi column, the sheet is Market and the Range is "AA7:aq26" when the listbox data is loaded. I have the below code but it only deletes the row from the list box not the source?

Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Long
Dim OriginalCount As Long

'Store original ListBox count
OriginalCount = ListBox3.ListCount



'Delete selected line items
For x = OriginalCount - 1 To 0 Step -1
If ListBox3.Selected(x) = True Then ListBox3.RemoveItem x
Next x

see below the form open in front of the spreadsheet sorcedata for the listbox.
Thanks!
Blake

1622669498281.png
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,232
Office Version
  1. 2007
Platform
  1. Windows
Can you put the code to see how you load the information in the listbox ListBox3?
 

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Can you put the code to see how you load the information in the listbox ListBox3?
Yes, here it is... I will explain this code... it is loaded in the worksheet_change event so when a drop down (Data Validation Drop downs) selects "From" it populates the spread sheet Market with the line of info from the Compare sheet, then the Listbox3. the first group populates the list box2 (in the below code) when "To" is selected, the listbox I am wanting to delete items by double clicking is listbox3

Thanks so much for your help, let me know if it would help if i share the file?
Blake


Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Value = "To" Then

Sheets("Market").Range("z5").Value = ActiveCell.Offset(0, 0).Value
Sheets("Market").Range("aa5").Value = ActiveCell.Offset(0, 1).Value
'Sheets("Market").Range("ab5").Value = ActiveCell.Offset(0, 2).Value
Sheets("Market").Range("ac5").Value = ActiveCell.Offset(0, 3).Value
Sheets("Market").Range("ad5").Value = ActiveCell.Offset(0, 4).Value
Sheets("Market").Range("ae5").Value = ActiveCell.Offset(0, 5).Value
Sheets("Market").Range("af5").Value = ActiveCell.Offset(0, 7).Value
Sheets("Market").Range("ag5").Value = ActiveCell.Offset(0, 8).Value
Sheets("Market").Range("ah5").Value = ActiveCell.Offset(0, 9).Value
If Sheets("Market").Range("ah5").Value = "" Then
Sheets("market").Range("ah5").Value = "-"
End If

Sheets("Market").Range("ai5").Value = ActiveCell.Offset(0, 10).Value
Sheets("Market").Range("aj5").Value = ActiveCell.Offset(0, 11).Value
Sheets("Market").Range("ak5").Value = ActiveCell.Offset(0, 12).Value
Sheets("Market").Range("al5").Value = ActiveCell.Offset(0, 13).Value
Sheets("Market").Range("am5").Value = ActiveCell.Offset(0, 14).Value
Sheets("Market").Range("an5").Value = "-"
ActiveCell.Offset(0, 2).Copy
Sheets("Market").Range("ab5").PasteSpecial
Sheets("Compare").Select
Application.CutCopyMode = False



ItemInfo.ListBox2.List = Sheets("Market").Range("aa5:am5").Value
ItemInfo.TextBox1.Text = "1"
ItemInfo.ListBox3.List = Sheets("Market").Range("aa7:aq26").Value
ItemInfo.Label24.Caption = Sheets("Market").Range("aq1").Value
ItemInfo.Label26.Caption = Sheets("Market").Range("aq2").Value
ItemInfo.Label47.Caption = Sheets("Market").Range("an1").Value
ItemInfo.Label50.Caption = Sheets("Market").Range("an2").Value
'ItemInfo.Label52.Caption = Sheets("Market").Range("at1").Value
'ItemInfo.Label54.Caption = Sheets("Market").Range("at2").Value



ItemInfo.Label24.Caption = Format(ItemInfo.Label24.Caption, "$#,###.00")
ItemInfo.Label26.Caption = Format(ItemInfo.Label26.Caption, "$#,###.00")
ItemInfo.Label47.Caption = Format(ItemInfo.Label47.Caption, "$#,###.00")
ItemInfo.Label50.Caption = Format(ItemInfo.Label50.Caption, "$#,###.00")
'ItemInfo.Label52.Caption = Format(ItemInfo.Label52.Caption, "$#,###.00")
'ItemInfo.Label54.Caption = Format(ItemInfo.Label54.Caption, "$#,###.00")







End If

If ActiveCell.Value = "From" Then

Dim lineoffset As Long
lineoffset = Sheets("compare").Range("a2").Value

Sheets("Market").Range("z6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 0).Value
Sheets("Market").Range("aa6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 1).Value
Sheets("Market").Range("ab6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 2).Value
Sheets("Market").Range("ac6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 3).Value
Sheets("Market").Range("ad6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 4).Value

Sheets("Market").Range("y6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 5).Value 'This is the original case volume
Sheets("Market").Range("af6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 7).Value
Sheets("Market").Range("ag6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 8).Value
Sheets("Market").Range("ah6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 9).Value
If Sheets("market").Range("ah6").Offset(lineoffset, 0).Value = "" Then
Sheets("market").Range("ah6").Offset(lineoffset, 0).Value = "-"
End If

Sheets("Market").Range("ai6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 10).Value
Sheets("Market").Range("aj6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 11).Value
Sheets("Market").Range("ak6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 12).Value
Sheets("Market").Range("al6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 13).Value
Sheets("Market").Range("am6").Offset(lineoffset, 0).Value = ActiveCell.Offset(0, 15).Value
Sheets("Market").Range("ao6").Offset(lineoffset, 0).Value = "1"



ItemInfo.ListBox3.List = Sheets("Market").Range("aa7:aq26").Value
ItemInfo.Label24.Caption = Sheets("Market").Range("aq1").Value
ItemInfo.Label26.Caption = Sheets("Market").Range("aq2").Value
ItemInfo.Label47.Caption = Sheets("Market").Range("an1").Value
ItemInfo.Label50.Caption = Sheets("Market").Range("an2").Value
'ItemInfo.Label52.Caption = Sheets("Market").Range("at1").Value
'ItemInfo.Label54.Caption = Sheets("Market").Range("at2").Value



ItemInfo.Label24.Caption = Format(ItemInfo.Label24.Caption, "$#,###.00")
ItemInfo.Label26.Caption = Format(ItemInfo.Label26.Caption, "$#,###.00")
ItemInfo.Label47.Caption = Format(ItemInfo.Label47.Caption, "$#,###.00")
ItemInfo.Label50.Caption = Format(ItemInfo.Label50.Caption, "$#,###.00")
'ItemInfo.Label52.Caption = Format(ItemInfo.Label52.Caption, "$#,###.00")
'ItemInfo.Label54.Caption = Format(ItemInfo.Label54.Caption, "$#,###.00")

ItemInfo.TextBox2.Text = Sheets("Market").Range("ao7").Value
ItemInfo.TextBox3.Text = Sheets("Market").Range("ao8").Value
ItemInfo.TextBox4.Text = Sheets("Market").Range("ao9").Value
ItemInfo.TextBox5.Text = Sheets("Market").Range("ao10").Value
ItemInfo.TextBox6.Text = Sheets("Market").Range("ao11").Value
ItemInfo.TextBox7.Text = Sheets("Market").Range("ao12").Value
ItemInfo.TextBox8.Text = Sheets("Market").Range("ao13").Value
ItemInfo.TextBox9.Text = Sheets("Market").Range("ao14").Value
ItemInfo.TextBox10.Text = Sheets("Market").Range("ao15").Value
ItemInfo.TextBox11.Text = Sheets("Market").Range("ao16").Value
ItemInfo.TextBox12.Text = Sheets("Market").Range("ao17").Value
ItemInfo.TextBox13.Text = Sheets("Market").Range("ao18").Value
ItemInfo.TextBox14.Text = Sheets("Market").Range("ao19").Value
ItemInfo.TextBox15.Text = Sheets("Market").Range("ao20").Value
ItemInfo.TextBox16.Text = Sheets("Market").Range("ao21").Value
ItemInfo.TextBox17.Text = Sheets("Market").Range("ao22").Value
ItemInfo.TextBox18.Text = Sheets("Market").Range("ao23").Value
ItemInfo.TextBox19.Text = Sheets("Market").Range("ao24").Value
ItemInfo.TextBox20.Text = Sheets("Market").Range("ao25").Value


End If
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,232
Office Version
  1. 2007
Platform
  1. Windows
ItemInfo.ListBox3.List = Sheets("Market").Range("aa7:aq26").Value

Some of these columns store unique values. That way before deleting the record in the listbox, you can look for that unique value in the column and delete the record on the sheet.
 

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

so are you saying to load some kind of search code in the double click event? to find the items and delete then on the sheet and then update the listbox?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,232
Office Version
  1. 2007
Platform
  1. Windows
Not quite.
We need to identify a key to identify the record on the sheet. Delete the record in the sheet and then delete the record in the listbox.
 

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ok, so the Key would be the customer # in the sheet Market column "AK" I think? that would be the unique field in that list... could we use that?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,232
Office Version
  1. 2007
Platform
  1. Windows
so the Key would be the customer # in the sheet Market column "AK" I think?
Apparently in your image I see 2 identical customers, so we cannot take that column. We need a unique key, that is, that the value is not repeated in the same column.
Another option is to read 2 columns, the customer and maybe another column that makes a unique key.
 

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
That was a bad example there will never be two identical customers in that column, if there is it will be a mistake and both will need to be deleted. I can put in some error handling if for some reason someone picks the same customer twice... that would be very rare...
 

blaker

New Member
Joined
Jul 1, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
not sure if this helps but here is the Initialize code how I have Listbox3 setup... will all 3 of the listboxes..


Private Sub UserForm_Initialize()
ItemInfo.StartUpPosition = 0
ItemInfo.Left = Application.Left + (0.5 * Application.Width) - (0.5 * ItemInfo.Width)
ItemInfo.Top = Application.Top + (0.5 * Application.Height) - (0.5 * ItemInfo.Height)


ItemInfo.Label2.Caption = Sheets("Compare").Range("c1").Value
ItemInfo.ListBox1.List = Sheets("Market").Range("U2:u60").Value
ItemInfo.ListBox1.ColumnCount = 3
ItemInfo.ListBox1.ColumnWidths = "90,35,25"


ItemInfo.ListBox2.ColumnCount = 14

ItemInfo.ListBox2.ColumnWidths = "25,50,200,50,40,50,25,30,100,0,0,0,0,0"
ItemInfo.ListBox3.ColumnCount = 17
ItemInfo.ListBox3.ColumnWidths = "25,50,200,50,40,50,25,30,100,135,55,80,2,40,0,0,40"

ItemInfo.Label26.Caption = Format(ItemInfo.Label26.Caption, "$#,###.00")
ItemInfo.Label58.Caption = Sheets("Convert").Range("s3").Value
ItemInfo.Label58.Caption = Format(ItemInfo.Label58.Caption, "$#,###.00")




End Sub
 

Forum statistics

Threads
1,136,207
Messages
5,674,415
Members
419,508
Latest member
trinstrick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top