Calling (Function?) from Userform_Initialize to generate list of unique values for Combo box

[Alan]

Hi All,

I would like to be able to call a (function?) from the
userform_initialize sub of a userform to populate a combo
with unique items.

I could do that separately in each userform, but since I have quite a
few that need to do it, I thought I would put the code in a separate
sub and just call it each time.

I am using a slightly modified sub I got from j-walk.com
(http://j-walk.com/ss/excel/tips/tip47.htm) - see below bottom
(basically just changed the variable names).

However, I am not sure how to call this from each userform_initialize
sub, return the list of unique items and use that list to populate the
combo.

Part of my problem is how (where?) to declare the variables so that
they exist in each place I need them and how to pass the unique list
back from the sub / function to the initialize code.

Hope that makes sense - feel free to ask for clarification!

Thanks,

Alan.



+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+


Option Explicit
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"


Function RemoveDuplicates()

Dim AllCells As Range, Cell As Range
Dim Brands_Unique As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in the Brands_Existing range
Set AllCells = Range("Database!H3:H5")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!

On Error Resume Next

For Each Cell In AllCells
Brands_Unique.Add Cell.Value, CStr(Cell.Value)
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To Brands_Unique.Count - 1
For j = i + 1 To Brands_Unique.Count
If Brands_Unique(i) > Brands_Unique(j) Then
Swap1 = Brands_Unique(i)
Swap2 = Brands_Unique(j)
Brands_Unique.Add Swap1, before:=j
Brands_Unique.Add Swap2, before:=i
Brands_Unique.Remove i + 1
Brands_Unique.Remove j + 1
End If
Next j
Next i



End Function


+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+

 

[keepITcool]

Alan,

Define your variable in a normal module
the module CAN have the option private module

Public Dim g_uni_items as collection

Public Function GetUnique(AllCells As Range, Cell As Range) as
Collection
Dim Brands_Unique As New Collection
Dim i As Integer, j As Integer
'...
set GetUnique = Brands_Unique
end Function

Then in your form or or in somewhere where you need to build the brand
list..

set g_uni_items=GetUniques(Range("a1:a100")

hth


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Alan wrote :

 

[Alan]

Alan,

Define your variable in a normal module
the module CAN have the option private module

Public Dim g_uni_items as collection

Public Function GetUnique(AllCells As Range, Cell As Range) as
Collection
Dim Brands_Unique As New Collection
Dim i As Integer, j As Integer
'...
set GetUnique = Brands_Unique
end Function

Then in your form or or in somewhere where you need to build the
brand list..

set g_uni_items=GetUniques(Range("a1:a100")

hth

Thanks for that - I will have a play!

Alan.