Removing items from a combobox 1

[DPlank]

I have a combo box which is populated at runtime with a list of usernames. When the form containing the combobox is called, the user list is added to the dropdowns (in 2 forms) in the manner shown below in function GetUserList. This seems to work OK. I want to dump the contents on the closing of the forms, so I wrote up EmptyUserList. I keep getting a strange error when the code gets to about the 4th iteration of the RemoveItem method.

Using Excel 97 - Help files are no use, they are asking me to obtain an updated help file from my vendor.

Here's what I got:

Public Function GetUserList()
Dim userList() As Variant
i = 0
Application.ScreenUpdating = False
Worksheets("AccessRights").Visible = True
Worksheets("AccessRights").Activate
With Worksheets("AccessRights").Range("A1:A65536")
    Set namefind = .Find("END", MatchCase:=True, Lookat:=xlWhole)
    If Not namefind Is Nothing Then
        numUsers = (namefind.Row) - 1
        
    End If
    ReDim userList(numUsers)
    For i = 1 To numUsers
        Cells(i, 1).Select
        userList(i) = Selection.Value
    Next i
    
End With
For i = 2 To numUsers
    ChangeAccess.userListing.AddItem (userList(i))
    ChangePass.userListing.AddItem (userList(i))
Next i
Worksheets("AccessRights").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Function

Public Function EmptyUserList()
Dim userList() As Variant
i = 0
Application.ScreenUpdating = False
Worksheets("AccessRights").Visible = True
Worksheets("AccessRights").Activate
With Worksheets("AccessRights").Range("A1:A65536")
    Set namefind = .Find("END", MatchCase:=True, Lookat:=xlWhole)
    If Not namefind Is Nothing Then
        numUsers = (namefind.Row) - 1
        
    End If
   ' ReDim userList(numUsers)
   ' For i = 1 To numUsers
   '     Cells(i, 1).Select
   '     userList(i) = Selection.Value
   ' Next i
End With
For j = 1 To (numUsers - 1)
    ChangeAccess.userListing.RemoveItem (j)
    ChangePass.userListing.RemoveItem (j)
Next j
Worksheets("AccessRights").Visible = xlVeryHidden
Application.ScreenUpdating = True

End Function

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[TonyJollans]

Hi DPlank,

When deleting it is always best to start from the end and work backwards, so ..

[blue]For j = (numUsers - 1) to 1 step -1
    ChangeAccess.userListing.RemoveItem (j)
    ChangePass.userListing.RemoveItem (j)
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DPlank]

Thanks Tony. I'll give it a try just now.

Hmmm. Now I'm getting a runtime error -
'-2147024809 (80070057)'
Invalid Argument.

Value of j at error is 7 (this is the first iteration of the loop)

Previously it made it through about 3 iterations before falling over.

This is just getting a bit weirder by the minute.

Dave


[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[TonyJollans]

Hi Dave,

I haven't looked at your code closely - but I think combobox itemns are a zero-based collection so is your value of j just too high?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DPlank]

I'll give it a try with a base zero version...

And...success!

The function now completes properly and returns control to its calling function.

Thanks Tony!

Star for the man....

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[TonyJollans]

Thanks, Dave,

Happy to help!!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.