ExcelBanter - Populate Combobox in VBA form from worksheet

Hi Xispo,

This is what I did a while ago (adapted for your task). It is a bit long.

1.) Create a user form "UserForm1"

2.) On "UserForm1" place the following controls: label "lbMeals", combobox
"cbMeals", label "lbFood" and combobox "cbFood"

3.) On sheet "Sheet1" enter the following values in the range A1:B15

breakfast eggs
breakfast bread
breakfast marmelade
breakfast coffee
lunch soup
lunch chicken
lunch vegetables
lunch icecream
lunch bread
lunch tea
dinner roast beaf
dinner rice
dinner lettuce
dinner coffee
dinner bread

4.) Back in VBE create a new module "Module1" and place the following code
(this is John Walkenbach's code slightly changed):

'---------Start Code--------
Option Explicit
' This code by John Walkenbach is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Public Sub RemoveDuplicates(InputRange As String, _
InputBox As Control, Optional InputFilter As Control)
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
Dim mControl As Control
Dim fControl As Control

Set AllCells = Range(InputRange)
Set mControl = InputBox
Set fControl = InputFilter

' 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
If fControl < 0 Then
For Each Cell In AllCells
If Cell.Offset(0, -1).Value = fControl.Value Then
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
End If
Next Cell
Else
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
End If

' Resume normal error handling
On Error GoTo 0

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

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
mControl.AddItem Item
Next Item
End Sub

Sub test()
UserForm1.Show
End Sub
'---------End Code--------

5.) Finally, in the "UserForm1" code module place the following code:

'---------Start Code--------
Option Explicit

Private Sub cbMeals_Change()
cbFood.Text = ""
If cbFood.ListCount < 0 Then
cbFood.Clear
End If
If cbMeals.Text = "" Then
cbFood.Enabled = False
lbFood.Enabled = False
Else
cbFood.Enabled = True
lbFood.Enabled = True
Call RemoveDuplicates("B1:B15", cbFood, cbMeals)
End If
End Sub

Private Sub UserForm_Initialize()
cbMeals.Enabled = True
lbMeals.Enabled = True
cbMeals.Text = ""

cbFood.Enabled = False
lbFood.Enabled = False
cbFood.Text = ""

Call RemoveDuplicates("A1:A15", cbMeals)
End Sub
'---------End Code--------

6.) Run the Sub Test ()

Hope this helps,
KL

"Xispo" wrote in message
...

Hi,

I have 2 combo box's on a form. "Meal" and "food".

After someone makes a selection in "meal", I want it to then search an
excel worksheet called lookups, and whereever it find the selected item
in column A, add the equivalent item from column C to the "food" combo
box.

I need it to loop until it has checked every item in column A and added
all necessary items to the "food" combo box.

Any help would be most appreciated.

Thanks.

--
Xispo
------------------------------------------------------------------------
Xispo's Profile:
http://www.excelforum.com/member.php...fo&userid=2527
View this thread: http://www.excelforum.com/showthread...hreadid=278834