Loading a unique list of names from a range into a combobox
 

Hi,

My plan is to have a form open when the workbook is opened, and the
form will contain a combobox with a list of names for the user to
choose from. The worksheet 'QueryBuster' is loaded with data, and
column N has all the assigned names. I want to look at column N and
extract a unique list of names and populate the combobox with that
list.

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.

What might I be doing wrong? Thanks for any help!


Private Sub UserForm_Initialize()

Dim rnNames As Range
Dim wsSheet As Worksheet
Dim wsNames As Worksheet
Dim vaNames As Variant ' the Names list stored as a variant
Dim vaItem As Variant 'a variant representing the type of
'items in ncData

Set wsSheet = Worksheets("QueryBuster")

With wsSheet
Set rnNames = .Range(.Range("N2"), .Range("N100000").End(xlUp))
End With

Set wsNames = Worksheets("Names")

With wsNames
rnNames.AdvancedFilter Action:=xlFilterCopy, _
CopytoRange:=.Range("A1"), Unique:=True

' store unique values in vaNames
vaNames = .Range(.Range("A1"), .Range("A500").End(xlUp)).Value

' clean up contents of names on Names sheet so it will be clean
next time
.Range("A:A").ClearContents
End With

'For Each vaItem In vaNames
' .AddItem vaNames(vaItem)
'Next vaItem

End Sub

Loading a unique list of names from a range into a combobox
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Loading a unique list of names from a range into a combobox
 

Try this in the userform code window...

Option Explicit

Private Sub UserForm_Initialize()
LoadComboList
End Sub

Sub LoadComboList()
Dim n&, sNames$, vNames
Const lNames& = 14 '//names column index

vNames = ThisWorkbook.Sheets("QueryBuster").UsedRange
For n = LBound(vNames) To UBound(vNames)
If InStr(sNames, vNames(n, lNames)) = 0 Then
sNames = sNames & "," & vNames(n, lNames)
End If
Next 'n
Me.ComboBox1.List = Split(Mid(sNames, 2), ",")
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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.

Loading a unique list of names from a range into a combobox
 

Hi! This worked too! Thank you!

Frank

On Sun, 24 May 2015 17:02:33 -0400, GS wrote:

Try this in the userform code window...

Option Explicit

Private Sub UserForm_Initialize()
LoadComboList
End Sub

Sub LoadComboList()
Dim n&, sNames$, vNames
Const lNames& = 14 '//names column index

vNames = ThisWorkbook.Sheets("QueryBuster").UsedRange
For n = LBound(vNames) To UBound(vNames)
If InStr(sNames, vNames(n, lNames)) = 0 Then
sNames = sNames & "," & vNames(n, lNames)
End If
Next 'n
Me.ComboBox1.List = Split(Mid(sNames, 2), ",")
End Sub