Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
I have two list boxes, (1) that initally holds the data and (1) that can be
added to: I first fill the List_AddFrom box with items, then check to see if a certain checkbox is true. If the Checkbox is true, it adds another item ("1st Piece Sample") into the List_AddFrom Box. Then I read in items from a sheet into the List_AddTo box. I then compare the two boxes and remove any duplication from the List_AddFrom box. This works great unless the the List_AddTo box reads in the "1st Piece Sample" from the sheet. Once it compares and removes the "1st Piece Sample" item from the List_AddFrom box, the next loop gives a List Property array index error (it comes from the LIst_AddFrom box). This happens regardless of where the "1st Piece Sample" is located in the box (first, last, middle). I have rewritten the counters several times (0 to .listcount -1, 0 to ..listcount, .Listcount to 0 step -1) and have even created internal counters (i = i-1, j = j-1, etc), to counter the error, but to no avail. How can I fix this? (Code below) Private Sub Load_NLBoxes() Dim i As Integer Dim j As Integer 'Load the List_AddFrom listbox with data If LAFStr = "" Then With List_AddFrom .RowSource = "" .Clear .AddItem "Rescinds PEC" .AddItem "Preventive Action" .AddItem "UL/CSA/CE Affected" .AddItem "Manual Change Required" .AddItem "S/N at Changeover" .AddItem "Cost Increase over 5%" End With CheckSample Else Exit Sub End If 'Validate List_AddFrom Listbox with List_AddTo listbox and remove items as necessary ' If List_AddFrom.ListIndex = -1 Then Exit Sub For i = 0 To List_AddFrom.ListCount - 1 For j = 0 To List_AddTo.ListCount - 1 If List_AddTo.List(j) = List_AddFrom.List(i) Then List_AddFrom.RemoveItem (i) i = i + 1 End If Next j Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
Hello asmenut, The problem lies in removing the item from the collection. The example will illustrate what happens when an item is remove... Code ------------------- LIST BEFORE AN ITEM IS REMOVED LIST AFTER AN ITEM IS REMOVE Index Value Index Value 0 "Rescinds PEC" 0 "Rescinds PEC" 1 "Preventive Action" 1 "Preventive Action" 2 "UL/CSA/CE Affected" 2 "UL/CSA/CE Affected" 3 "Manual Change Required" 3 "Manual Change Required" 4 "S/N at Changeover" 4 "S/N at Changeover" 5 "Cost Increase over 5%" 5 "Cost Increase over 5%" ------------------- -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=47848 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
Are we supposed to see a difference? Or are you saying there is no
difference. Maybe I missed it, but the left and right sides look identical. -- Regards, Tom Ogilvy "Leith Ross" wrote in message ... Hello asmenut, The problem lies in removing the item from the collection. The examples will illustrate what happens when an item is remove... Code: -------------------- LIST BEFORE AN ITEM IS REMOVED LIST AFTER AN ITEM IS REMOVED Index Value Index Value 0 "Rescinds PEC" 0 "Rescinds PEC" 1 "Preventive Action" 1 "Preventive Action" 2 "UL/CSA/CE Affected" 2 "UL/CSA/CE Affected" 3 "Manual Change Required" 3 "Manual Change Required" 4 "S/N at Changeover" 4 "S/N at Changeover" 5 "Cost Increase over 5%" 5 "Cost Increase over 5%" -------------------- -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478481 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
Yes there is a difference. Let's say that the sheet had the following data
that was read into the List_AddTo box: Preventive Action Rescinds PEC 1st Piece Sample Required The Code compares the items and if the strings match, then the item in the List_AddFrom box is removed. Based upon the original List_AddFrom list: Rescinds PEC Preventive Action UL/CSA/CE Affected Manual Change Required S/N at ChangeOver Cost Increase over 5% Iset Piece Sample Required (This is if CheckSample is true) and comparing the "read in" items (List_AddFrom), the resulting lists should be as follows: Index List_AddFrom Value Index List_AddTo Value 0 "UL/CSA/CE Affected" 0 "Preventive Action" 1 "Manual Change Required" 1 "Rescinds PEC" 2 "S/N at Changeover" 2 "1st Piece Sample Required" 3 "Cost Increase over 5%" "Tom Ogilvy" wrote: Are we supposed to see a difference? Or are you saying there is no difference. Maybe I missed it, but the left and right sides look identical. -- Regards, Tom Ogilvy "Leith Ross" wrote in message ... Hello asmenut, The problem lies in removing the item from the collection. The examples will illustrate what happens when an item is remove... Code: -------------------- LIST BEFORE AN ITEM IS REMOVED LIST AFTER AN ITEM IS REMOVED Index Value Index Value 0 "Rescinds PEC" 0 "Rescinds PEC" 1 "Preventive Action" 1 "Preventive Action" 2 "UL/CSA/CE Affected" 2 "UL/CSA/CE Affected" 3 "Manual Change Required" 3 "Manual Change Required" 4 "S/N at Changeover" 4 "S/N at Changeover" 5 "Cost Increase over 5%" 5 "Cost Increase over 5%" -------------------- -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478481 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
Hello asmenut, Change the validation loop code to this... Code: -------------------- Dim N As Long For j = 0 To List_AddTo.ListCount - 1 If List_AddTo.List(j) = List_AddFrom.List(N) Then List_AddFrom.RemoveItem (N) Else N = N + 1 End If Next j -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478481 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox counter help
Leith,
Thanks. That's the ticket. Once I read through your modification, I realized that I made a major error doing a dual "For/Next" loop. One loop and a separate counter was the more correct way to approach the problem. It is good to learn. Experience comes from making mistakes, wisdom comes from not repeating them. "Leith Ross" wrote: Hello asmenut, Change the validation loop code to this... Code: -------------------- Dim N As Long For j = 0 To List_AddTo.ListCount - 1 If List_AddTo.List(j) = List_AddFrom.List(N) Then List_AddFrom.RemoveItem (N) Else N = N + 1 End If Next j -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478481 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
Modification of listbox to listbox code | Excel Programming | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |