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

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

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

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

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

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