Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading a unique list of names from a range into a combobox
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading a unique list of names from a range into a combobox
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Unique Values in as a combobox value list | Excel Discussion (Misc queries) | |||
trouble loading a range into a comboBox | Excel Programming | |||
Loading sheet names in Combobox | Excel Programming | |||
how to populate a combobox with a list of unique values? | Excel Programming | |||
Populate unique list in combobox | Excel Programming |