Hey Leo,

Thanks for the reply.

Sorry for not including more info. With this post I hope to remedy
that.

It worked, sort of. But the second ComboBox (the second pass of the
sub) gives me that pesky permission denied error when I try to load
the ComboBox4 with the newly reloaded Collection. Here is the code
that I am using:

Option Explicit
Dim Rng1 As Range
Dim Rng4 As Range
Dim Rng5 As Range
Dim item As Variant
Dim wks As Worksheet
__________________________________________________ _________

Sub DefaultSet()
Set wks = ThisWorkbook.Sheets("Data")
Set Rng1 = wks.Range("NamedRange1")
Set Rng4 = wks.Range("NamedRange4")
Set Rng5 = wks.Range("NamedRange5")
End Sub
__________________________________________________ _________

Sub NonDuplicateList(lPass As Long)
Dim cmBox As ComboBox
Dim NoDupes As Collection
Dim rRng As Range
Dim lRow As Long
Dim i As Long

Set NoDupes = New Collection
Select Case lPass
Case 1
Set rRng = Rng1
Set cmBox = TB1
Case 4
Set rRng = Rng4
Set cmBox = TB4
Case 5
Set rRng = Rng5
Set cmBox = TB5
End Select

'\\ Load the NoDupes Collection
On Error Resume Next
For lRow = 1 To rRng.Rows.Count
With rRng(lRow)
NoDupes.Add .Value, CStr(.Value)
End With
Next lRow
On Error GoTo 0

'\\ Load the ComboBox
For Each item In NoDupes
'<Debug kicks me out here on the 1st pass of the 2nd Call
cmBox.AddItem item
Next item

'\\ Removes the first item every cycle until empty
For i = 1 To NoDupes.Count
NoDupes.Remove 1
Next i

'\\ Clears memory
Set NoDupes = Nothing

End Sub
__________________________________________________ _________

Private Sub UserForm_Initialize()
DefaultSet
Call NonDuplicateList(1)
Call NonDuplicateList(4)
Call NonDuplicateList(5)
' (there are many more, however this is enough for this example
End Sub

Maybe I doing it wrong, I don't know. All I know is, it does the
first "Call NonDuplicateLost(1)" no problem. But on the next call
(4), debug kicks it out, at the marked line, with that permission
denied error.

I am hoping someone can show me a work around.

TIA

-Minitman



On Thu, 27 Jul 2006 13:50:09 +0200, "Leo Heuser"
wrote:

"Minitman" skrev i en meddelelse
.. .
Greetings,

Norman Jones helped me with a neat trick using a Collection to remove
duplicate entries from a range. I works great - for the first range -
after that it keeps giving a permission denied error. I think it
might have something to do with the method of resetting the
collection.

Anyone know the best way to load a ComboBox with the contents of a
collection and then reset the collection to fill the next ComboBox?

Any help is much appreciated.

TIA

-Minitman


Set MyCollection = Nothing

--
Best regards
Leo Heuser