Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collection Key | Excel Programming | |||
The Pictures Collection | Excel Programming | |||
collection of sheets | Excel Programming | |||
Collection | Excel Programming | |||
Reset New Collection | Excel Programming |