I have an Excel workbook with various sheets!
(1) sheet (Recipe Input) has a form named frmRecipeCostingData with a
Combobox named (Ingredient)
Textbox named (Unit)
Textbox named (Unit Cost)
Another sheet named (Food Inventory) has
columns A (Ingredient), G (Unit), & I (Unit Cost)
I want to use the combobox in the form to lookup
the ingredient on Food Inventory Sheet and pull the corresponding data in
column G & I into the form.
Where do I start?
Hi,
I did something similar a while back - this might get you started.
The code below populates a combo box from data in a spreadsheet. When
you said that one sheet has a form, etc.. did you mean that there is a
trigger (button) on that sheet to open that form?
I've subbed your sheet name in the first bit of code below - basically
in the Private Sub cboCourse_Change() section, you can specify what
will happen when that combo box (Ingredient) is changed.
Hope this is helpful,
Louis
----------------------
private sub UserForm_Initialize()
Dim MyUniqueList
'////combo box setup
'Set up primary (first) combo box (cboCourse)
With Me.cboCourse
'.Clear ' clear the listbox content
MyUniqueList = UniqueItemList1(Range("Food Inventory!A1:A300"),
True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next
i = 0
.ListIndex = 0 ' select the first item
End With
'////Background code for combo box population
'////No Editing
Private Function UniqueItemList1(InputRange1 As Range, _
HorizontalList1 As Boolean) As Variant
Dim cl_1 As Range, cUnique1 As New Collection, i As Long, uList1()
As Variant
Application.Volatile
On Error Resume Next
'////first collection (cUnique) for including blank spaces and
'////maintaining index count for spreadsheet
For Each cl_1 In InputRange1
'If cl.Formula <> "" Then
cUnique1.Add cl_1.Value, CStr(cl_1.Value)
'End If
Next cl_1
'////set up count for spreadsheet
UniqueItemList1 = ""
If cUnique1.Count > 0 Then
ReDim uList1(1 To cUnique1.Count)
For i = 1 To cUnique1.Count
uList1(i) = cUnique1(i)
Next i
UniqueItemList1 = uList1
If Not HorizontalList1 Then
UniqueItemList1 = _
Application.WorksheetFunction.Transpose(UniqueItemList1)
End If
End If
On Error GoTo 0
End Function
Private Sub cboCourse_Change()
'Check if ListIndex = 0 (first option in combo box)
With Me.cboCourse2
'.Clear
If Me.cboCourse.ListIndex = 0 Then
Label4.Visible = False
Me.cboCourse2.Visible = False
..Clear ' Clear the list box content
'MyUniqueList3 = UniqueItemList2(Sheet1.Range("B8:B12"), True)
'For i = 1 To UBound(MyUniqueList3)
'.AddItem MyUniqueList3(i)
'Next i
'Me.cboCourse2.ListIndex = 0
End If
End With
'Check if ListIndex = 1 or "all" (second option in combo box)
With Me.cboCourse2
'.Clear
If Me.cboCourse.ListIndex = 1 Then
Me.Label4.Visible = True
Me.cboCourse2.Visible = True
..Clear ' Clear the list box content
'////Edit range below
'the variable below is to populate the combo box
MyUniqueList2 = UniqueItemList2(Sheet1.Range("B8:B400"), True)
'////End Edit
'the variable below will have spaces in the listing - for
'an accurate index count of row position
'MyUniqueList2a = UniqueItemList1(Sheet1.Range("B8:B385"), True)
'loop below for combo box
For i = 1 To UBound(MyUniqueList2)
..AddItem MyUniqueList2(i)
Next i
'i = 0
Me.cboCourse2.ListIndex = 0 'select the first item
End If
End With
