Thread
:
Loading a unique list of names from a range into a combobox
View Single Post
#
4
Posted to microsoft.public.excel.programming
[email protected]
external usenet poster
Posts: 13
Loading a unique list of names from a range into a combobox
Hi! That worked! Thank you very much!
Frank
On Sun, 24 May 2015 19:12:52 +0200, Claus Busch
wrote:
Hi,
Am Sun, 24 May 2015 12:20:53 -0400 schrieb
:
I've found multiple examples in my Google search, but I can't seem to
make this work. In my code below, I'm trying to take column N (the
names) in the QueryBuster sheet, and copy the unique filtered names to
A1 on the Names sheet. Then from there, load the combo box with that
list of names.
there are many ways to do this. Here are two suggestions:
Method1:
Private Sub UserForm_Initialize()
Dim wsh1 As Worksheet, wsh2 As Worksheet
Dim LRow As Long
Set wsh1 = Worksheets("QueryBuster")
Set wsh2 = Worksheets("Names")
wsh1.Range("N:N").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=wsh2.Range("A1"), Unique:=True
LRow = wsh2.Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.RowSource = wsh2.Name & "!" & Range("A2:A" & LRow).Address
End Sub
Method2:
Private Sub UserForm_Initialize()
Dim wsh1 As Worksheet, wsh2 As Worksheet
Dim LRow As Long, i As Long
Dim myDic As Object
Dim vardata As Variant, varOut As Variant
Set wsh1 = Worksheets("QueryBuster")
Set wsh2 = Worksheets("Names")
LRow = wsh1.Cells(Rows.Count, "N").End(xlUp).Row
vardata = wsh1.Range("N2:N" & LRow)
Set myDic = CreateObject("scripting.dictionary")
For i = LBound(vardata) To UBound(vardata)
myDic(vardata(i, 1)) = vardata(i, 1)
Next
varOut = myDic.items
For i = LBound(varOut) To UBound(varOut)
Me.ComboBox1.AddItem varOut(i)
Next
End Sub
Regards
Claus B.
Reply With Quote
[email protected]
View Public Profile
Find all posts by
[email protected]