Reset Collection
 

"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

Followup to newsgroup only please.

Reset Collection
 

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

Reset Collection
 

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

Hi. I don't have a solution to your error. However, I'm kind of a fan of
the Dictionary Object.
Just throwing this our as an idea...

Sub Demo()
Dim Dic As Object
Dim Cell As Range
Set Dic = CreateObject("Scripting.Dictionary")

On Error Resume Next
For Each Cell In [A1:A10]
Dic.Add Cell.Value, 1
Next
On Error GoTo 0
'Load to ComboBox1
ComboBox1.List() = Dic.keys
'Clear Dictionary
Dic.RemoveAll
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Minitman" wrote in message
...
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

Reset Collection
 

Hey Dana,

Thank you for the reply.

That is a neat solution. I am not sure if it is what I need on this
section of my project, but I am seeing a few possibilities for other
areas. It might even be a solution to this problem, I'm not sure -
I'll have to play with this and see where it leads.

Thank you, I am always looking for different solutions to old
problems. Each solution has it's own limitations and sometimes a
different approach will go beyond the limitation of the current
standard solution.

-Minitman

On Thu, 27 Jul 2006 17:10:02 -0400, "Dana DeLouis"
wrote:

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

Hi. I don't have a solution to your error. However, I'm kind of a fan of
the Dictionary Object.
Just throwing this our as an idea...

Sub Demo()
Dim Dic As Object
Dim Cell As Range
Set Dic = CreateObject("Scripting.Dictionary")

On Error Resume Next
For Each Cell In [A1:A10]
Dic.Add Cell.Value, 1
Next
On Error GoTo 0
'Load to ComboBox1
ComboBox1.List() = Dic.keys
'Clear Dictionary
Dic.RemoveAll
End Sub