User Form Compile Error on simple step

[bdmangum]

I'm totally confused as to why an error would occur when it does in my code. The wierdest part is that it already run the code once during the loop.

Let me explain real quick what happens above this section of code. the code auto-creates a combobox and then fills it with data from Sheet4. Now in the code below I'm trying to delete any items in the box which are also listed in sheet2, thus making sure the user can't pick them.

Here's the code with the error line in bold:

compcount = 3
quickcount = 10
While Worksheets(2).Cells(compcount, TempCount + 1).Value <> ""
    With cNewComboBox
        cNewComboBox.ListIndex = 0
        While cNewComboBox.ListIndex <> ""
            On Error GoTo ErrorHandler
            [b]cNewComboBox.ListIndex = cNewComboBox.ListIndex + 1[/b]
            On Error Resume Next
        Wend

ErrorHandler:
        TempIndex = cNewComboBox.ListIndex
        While TempIndex >= 0
            If Worksheets(2).Cells(compcount, TempCount + 1).Value = cNewComboBox.Value Then
                cNewComboBox.RemoveItem (TempIndex)
                cNewComboBox.ListIndex = TempIndex
            End If
            TempIndex = TempIndex - 1
            cNewComboBox.ListIndex = cNewComboBox.ListIndex - 1
        Wend
    End With
    compcount = compcount + 1
Wend

The part which has me confused is that it always gets the error the second time through the loop right when it tries to increment to the last ListIndex. It doesn't matter what the last ListIndex is either. If I set the data in Sheet2 so that there are only 2 items (making the max ListIndex = 1) it stills stops before the last increment (ListIndex=0). If I set the data in sheet2 to 10 items, it stops before the last increment; this time the max ListIndex = 9 and it stops at 8.

The error I get is "Run-Time 440" "Automation Error".

Any thoughts?

 

[bdmangum]

Hehe, never mind. I figured out an easier way to do this. I'm not sure why I didn't see this ruote earlier. I just realized it would be way easier to simply compare the data before I insert it into the combobox.

Here's what i ended up doing:

compcount = wcount + 3
While Worksheets(4).Cells(compcount, quickcount).Value <> ""
    checkcount = 3
    While Worksheets(2).Cells(checkcount, TempCount + 1).Value <> ""
        checkstring = Cells(checkcount, TempCount + 1).Value
        
        If checkstring <> Worksheets(4).Cells(compcount, quickcount).Value Then
            cNewComboBox.AddItem Worksheets(4).Cells(compcount, quickcount).Value
        End If
        
        checkcount = checkcount + 1
    Wend
    
    compcount = compcount + 1
    
Wend

 

[PHV]

What about this (typed, untested) ?

compcount = 3
quickcount = 10
While Worksheets(2).Cells(compcount, TempCount + 1).Value <> ""
  With cNewComboBox
    For i = .ListCount - 1 To 0 Step -1
      If Worksheets(2).Cells(compcount, TempCount + 1).Value = .List(i) Then
        .RemoveItem(i)
      End If
    Next i
  End With
  compcount = compcount + 1
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdmangum]

Thanks PH! Works great. I think I'll go with your code. The other way i posted in my second code still had some glitches. It looped to many times and thus inserted the same item abuot 8 times. lol. Anyways thanks.