Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove from listbox
Hi,
I'm using the code below to copy the selected range in a multiple selection listbox to another listbox. After copying the values in the selection I would like to remove these values from the first Listbox, how do I do this? Cheers, Paul Private Sub CommandButton2_Click() Dim ind As Integer Dim tot_items As Integer Dim lastrow As Integer s = 7 d = 0 b = ActiveCell.Row a = ActiveCell.Column + 7 tot_items = ListBox4.ListCount lastrow = Sheets("X").Cells(Rows.Count, 5).End(xlUp).Row Application.ScreenUpdating = False For ind = 0 To tot_items - 1 If ListBox4.Selected(ind) Then d = d + 1 a = a + 1 Cells(b, a).Value = ListBox4.List(ind) ActiveCell.Value = ActiveCell.Offset(0, 8).Value Worksheets(5).Cells(lastrow, 5).Value = ListBox4.List(ind) lastrow = lastrow + 1 End If Next ind ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, (d + s)).Value With frm_TDL Dim x As Integer Dim i As Long For x = 0 To tot_items - 1 If ListBox4.Selected(x) = True Then ListBox9.AddItem ListBox4.List(x, 0) ListBox4.Selected(x) = False End If Next For i = Me.ListBox4.ListCount - 1 To 0 Step -1 If Me.ListBox4.Selected(i) Then Me.ListBox4.RemoveItem (i) End If Next i End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove from listbox
In VBA help files, It states that if a range was entered in the ListFill
property to load the list box then the RemoveItem method fails. Also, if you want to remove all items from the list box, then: Use the RemoveAllItems method to remove all entries from a Microsoft Excel list box or combo box. Use the Clear method to remove all items from an ActiveX list box or combo box. "Paul" wrote: Hi, I'm using the code below to copy the selected range in a multiple selection listbox to another listbox. After copying the values in the selection I would like to remove these values from the first Listbox, how do I do this? Cheers, Paul Private Sub CommandButton2_Click() Dim ind As Integer Dim tot_items As Integer Dim lastrow As Integer s = 7 d = 0 b = ActiveCell.Row a = ActiveCell.Column + 7 tot_items = ListBox4.ListCount lastrow = Sheets("X").Cells(Rows.Count, 5).End(xlUp).Row Application.ScreenUpdating = False For ind = 0 To tot_items - 1 If ListBox4.Selected(ind) Then d = d + 1 a = a + 1 Cells(b, a).Value = ListBox4.List(ind) ActiveCell.Value = ActiveCell.Offset(0, 8).Value Worksheets(5).Cells(lastrow, 5).Value = ListBox4.List(ind) lastrow = lastrow + 1 End If Next ind ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, (d + s)).Value With frm_TDL Dim x As Integer Dim i As Long For x = 0 To tot_items - 1 If ListBox4.Selected(x) = True Then ListBox9.AddItem ListBox4.List(x, 0) ListBox4.Selected(x) = False End If Next For i = Me.ListBox4.ListCount - 1 To 0 Step -1 If Me.ListBox4.Selected(i) Then Me.ListBox4.RemoveItem (i) End If Next i End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove from listbox
Thanks for clearing that up for me, I'm not happy with it but.........S H!
I was really hoping I could remove the selection only! Another question you might answer for me; I've got a list of numbers listed in a sheet from C1:C1024, With the worksheet change event; Whenever I enter a value in a cell in column 6, the code will search for that value in above mentioned range. When he's found it he will change this values' font. The same thing when I enter a value in column 7. Is it possible for the numbers in between the two values to adjust to the same font via code? So I'm entering number 1 in column 6, this value that's in C1 changes font. Then I'm entering number 7 in column 7, this value in C7 also changes font, but I would like that the values in C2 tru C6 also change in the specified font. Is this possible? Cheers, Paul "JLGWhiz" wrote: In VBA help files, It states that if a range was entered in the ListFill property to load the list box then the RemoveItem method fails. Also, if you want to remove all items from the list box, then: Use the RemoveAllItems method to remove all entries from a Microsoft Excel list box or combo box. Use the Clear method to remove all items from an ActiveX list box or combo box. "Paul" wrote: Hi, I'm using the code below to copy the selected range in a multiple selection listbox to another listbox. After copying the values in the selection I would like to remove these values from the first Listbox, how do I do this? Cheers, Paul Private Sub CommandButton2_Click() Dim ind As Integer Dim tot_items As Integer Dim lastrow As Integer s = 7 d = 0 b = ActiveCell.Row a = ActiveCell.Column + 7 tot_items = ListBox4.ListCount lastrow = Sheets("X").Cells(Rows.Count, 5).End(xlUp).Row Application.ScreenUpdating = False For ind = 0 To tot_items - 1 If ListBox4.Selected(ind) Then d = d + 1 a = a + 1 Cells(b, a).Value = ListBox4.List(ind) ActiveCell.Value = ActiveCell.Offset(0, 8).Value Worksheets(5).Cells(lastrow, 5).Value = ListBox4.List(ind) lastrow = lastrow + 1 End If Next ind ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, (d + s)).Value With frm_TDL Dim x As Integer Dim i As Long For x = 0 To tot_items - 1 If ListBox4.Selected(x) = True Then ListBox9.AddItem ListBox4.List(x, 0) ListBox4.Selected(x) = False End If Next For i = Me.ListBox4.ListCount - 1 To 0 Step -1 If Me.ListBox4.Selected(i) Then Me.ListBox4.RemoveItem (i) End If Next i End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove from listbox
It is possible but it is a more complex procedure than I am in the mood to
write. As I currently visualize it, it would involve InputBoxes incorporated into a Worksheet_Change event procedure. I just did a more simple version for a single column yesterday. Don't have any feed back on it yet. "Paul" wrote: Thanks for clearing that up for me, I'm not happy with it but.........S H! I was really hoping I could remove the selection only! Another question you might answer for me; I've got a list of numbers listed in a sheet from C1:C1024, With the worksheet change event; Whenever I enter a value in a cell in column 6, the code will search for that value in above mentioned range. When he's found it he will change this values' font. The same thing when I enter a value in column 7. Is it possible for the numbers in between the two values to adjust to the same font via code? So I'm entering number 1 in column 6, this value that's in C1 changes font. Then I'm entering number 7 in column 7, this value in C7 also changes font, but I would like that the values in C2 tru C6 also change in the specified font. Is this possible? Cheers, Paul "JLGWhiz" wrote: In VBA help files, It states that if a range was entered in the ListFill property to load the list box then the RemoveItem method fails. Also, if you want to remove all items from the list box, then: Use the RemoveAllItems method to remove all entries from a Microsoft Excel list box or combo box. Use the Clear method to remove all items from an ActiveX list box or combo box. "Paul" wrote: Hi, I'm using the code below to copy the selected range in a multiple selection listbox to another listbox. After copying the values in the selection I would like to remove these values from the first Listbox, how do I do this? Cheers, Paul Private Sub CommandButton2_Click() Dim ind As Integer Dim tot_items As Integer Dim lastrow As Integer s = 7 d = 0 b = ActiveCell.Row a = ActiveCell.Column + 7 tot_items = ListBox4.ListCount lastrow = Sheets("X").Cells(Rows.Count, 5).End(xlUp).Row Application.ScreenUpdating = False For ind = 0 To tot_items - 1 If ListBox4.Selected(ind) Then d = d + 1 a = a + 1 Cells(b, a).Value = ListBox4.List(ind) ActiveCell.Value = ActiveCell.Offset(0, 8).Value Worksheets(5).Cells(lastrow, 5).Value = ListBox4.List(ind) lastrow = lastrow + 1 End If Next ind ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, (d + s)).Value With frm_TDL Dim x As Integer Dim i As Long For x = 0 To tot_items - 1 If ListBox4.Selected(x) = True Then ListBox9.AddItem ListBox4.List(x, 0) ListBox4.Selected(x) = False End If Next For i = Me.ListBox4.ListCount - 1 To 0 Step -1 If Me.ListBox4.Selected(i) Then Me.ListBox4.RemoveItem (i) End If Next i End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove from listbox
A followup for the first portion: Why don't you drop the .rowsource and just
use .additem or load the .list as an array. And I don't understand what's going on in the second portion. Paul wrote: Thanks for clearing that up for me, I'm not happy with it but.........S H! I was really hoping I could remove the selection only! Another question you might answer for me; I've got a list of numbers listed in a sheet from C1:C1024, With the worksheet change event; Whenever I enter a value in a cell in column 6, the code will search for that value in above mentioned range. When he's found it he will change this values' font. The same thing when I enter a value in column 7. Is it possible for the numbers in between the two values to adjust to the same font via code? So I'm entering number 1 in column 6, this value that's in C1 changes font. Then I'm entering number 7 in column 7, this value in C7 also changes font, but I would like that the values in C2 tru C6 also change in the specified font. Is this possible? Cheers, Paul "JLGWhiz" wrote: In VBA help files, It states that if a range was entered in the ListFill property to load the list box then the RemoveItem method fails. Also, if you want to remove all items from the list box, then: Use the RemoveAllItems method to remove all entries from a Microsoft Excel list box or combo box. Use the Clear method to remove all items from an ActiveX list box or combo box. "Paul" wrote: Hi, I'm using the code below to copy the selected range in a multiple selection listbox to another listbox. After copying the values in the selection I would like to remove these values from the first Listbox, how do I do this? Cheers, Paul Private Sub CommandButton2_Click() Dim ind As Integer Dim tot_items As Integer Dim lastrow As Integer s = 7 d = 0 b = ActiveCell.Row a = ActiveCell.Column + 7 tot_items = ListBox4.ListCount lastrow = Sheets("X").Cells(Rows.Count, 5).End(xlUp).Row Application.ScreenUpdating = False For ind = 0 To tot_items - 1 If ListBox4.Selected(ind) Then d = d + 1 a = a + 1 Cells(b, a).Value = ListBox4.List(ind) ActiveCell.Value = ActiveCell.Offset(0, 8).Value Worksheets(5).Cells(lastrow, 5).Value = ListBox4.List(ind) lastrow = lastrow + 1 End If Next ind ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, (d + s)).Value With frm_TDL Dim x As Integer Dim i As Long For x = 0 To tot_items - 1 If ListBox4.Selected(x) = True Then ListBox9.AddItem ListBox4.List(x, 0) ListBox4.Selected(x) = False End If Next For i = Me.ListBox4.ListCount - 1 To 0 Step -1 If Me.ListBox4.Selected(i) Then Me.ListBox4.RemoveItem (i) End If Next i End With -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox remove item according to the value | Excel Programming | |||
Remove items from a listbox | Excel Programming | |||
listbox remove Item | Excel Programming | |||
Remove all Listbox items | Excel Programming | |||
Remove data with listbox | Excel Programming |