Reset Collection

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

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
 
Minitman said:
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
Click to expand...


Set MyCollection = Nothing
 
Permission error on a combo often arises because it is set to a worksheet
range. What does the code look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Sub abc()
Dim col As Collection
For i = 1 To 10
' Set col = Nothing
Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

worked with or without the commented out line. (xl2003)
 
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



Set MyCollection = Nothing
Click to expand...
 
Hey Bob,

Thanks for the reply.

I just sent the code with my last reply.

Please, feel free to take a peak at it.

-Minitman
 
Anyone know the best way to load a ComboBox with the contents of a
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 said:
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



Set MyCollection = Nothing
Click to expand...
Click to expand...
 
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

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
Click to expand...
 
Hey Tom,

Thanks for the reply. It's good to hear from you again.

This is an interesting sub.

I wasn't aware of Debug.Print, and what all it does. After the
reference I checked the M$ help file for more info. I think I can use
that.

My code is similar to yours so I gave up and went to prepare a small
sample workbook to send. As I was down sizing my workbook (I started
with deleting a sheet and removing linked named ranges) I discovered
that most of my dynamic named ranges for the remaining page were
slightly corrupted. There was enough there for them to load into
variables and load items into a Collection, but not enough to come out
of the collection into the ComboBox. THAT was the problem. Once the
named ranges were corrected, the code now works fine.

Again, thank you.

-Minitman
 
Tom Ogilvy said:
Sub abc()
Dim col As Collection
For i = 1 To 10
' Set col = Nothing
Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

worked with or without the commented out line. (xl2003)
Click to expand...


Sub abc()
Dim col As New Collection
For i = 1 To 10
Set col = Nothing
' Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

also works with or without the commented out line. (xl2003)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HPageBreak not working 4
XL2003 Userform; cmbbox uses named range source, changes unexpecte 1
Excel Accumulator Needs Some Tweaking 0
Load UserForm ComboBox And Autofill 124 TextBoxes 2
Reset New Collection 2
VBA Collections 13
Unique values in a Collection 4
Raise Event Problem 4

Back
Top