Deleting listbox list, from listbox and source

B

blaker

New Member
Joined
Jul 1, 2013
Messages
32
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Can you put the code to see how you load the information in the listbox ListBox3?
 
Upvote 0
DanteAmor said:
Can you put the code to see how you load the information in the listbox ListBox3?
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
blaker said:
so the Key would be the customer # in the sheet Market column "AK" I think?
Click to expand...
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Deleting inside a listbox with 2 columns
Replies
1
Views
376
xsmurf
X
C
  • Question
Excel Userform Listbox Search issues
Replies
0
Views
74
cerberus1845
C
M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
195
maiatanaka
M
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
306
pure vito
P
I
  • Question
Listbox not being populate when code is run
Replies
4
Views
252
ipbr21054
I

Forum statistics

Threads
1,214,430
Messages
6,119,447
Members
448,898
Latest member
drewmorgan128

Share this page

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
Back
Top