Hi,
New to the forum so please go easy
I've written an Excel (using 2007, compatibility mode to 2003) app for our stock control. Basically it's a protected stock sheet which the user adds/removes stock via a form.
When the user runs the form the following code loops through the stock list header adding item names to a combo box. Problem is I get a run-time error if there's more than 60+ items but while the error always occurs, it happens at different places in additems.
Run-time error '-2147024809 (80070057)
Invalid argument.
In the code I've allowed a loop to 1000 items but in reality they'll be no more than 150.
Private Sub UserForm_Initialize()
'Add the stock item names to the combobox.
Blank_Items = 0
For i = 2 To 1000
If Worksheets("Stock List").Cells(4, i).Value = "" Or Worksheets("Stock List").Cells(4, i).Value = 0 Then
Blank_Items = Blank_Items + 1 'Count consecutive blank items.
GoTo Skip_Item 'If blank, don't add to list.
End If
With cbStock_Item
.AddItem Worksheets("Stock List").Cells(4, i).Value, i - 2 'Add each item name from the stocklist and index from place 0 in listbox.
End With
Blank_Items = 0 'Resets the blank counter as a valid item was found.
Skip_Item:
If Blank_Items = 3 Then '3 blanks and assume at the end of the items.
GoTo No_More_Items
End If
Next i
No_More_Items:
End Sub
Looking forward to some sage advice.
Cheers,
Mark
Bookmarks