Vba Code Combobox Lookup and Return all Value not jus the First value.

TonySondergeld

New Member
Joined
Jul 11, 2009
Messages
31
I Have 2 Comboboxs, the first Combobox 1 choose the Product from a list of products, when the product is selected. it lookup a range (for example from sheet 2 H4:G1500) it then match all products that match the First combobox value and return all value from the second column of the range and place it into the Combobox 2
Code so far, will place number in Combobox 2 but only the first entry.
Dim myRange As Range
Sheets("Sheet").Combobox1.Clear
Set myRange = Sheets("Sheet2").Range("G4:H1500")
i = Combobox1.Value
Combobox2.Value = Application.VLookup(i, myRange, 2, False)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
VLOOKUP is not suitable for this. If I understand what you mean then probably the best way would be to use AdvancedFilter and create a temporary list of matches which can be deleted after the combobox is loaded.

Kave you actually got 1500 rows of data or is that a random number/
 

TonySondergeld

New Member
Joined
Jul 11, 2009
Messages
31

ADVERTISEMENT

It is a stock list of production for byproduct, so every bag that is produced is given a number that is random sequence, So I would Like to select the Product with the first combobox1 and then have the sequence numbers load into the combobox2 so when the correct number is select it will finish filling out the Form details for the product and print off the product flag. That last part work great when pulling number in manually.
 

TonySondergeld

New Member
Joined
Jul 11, 2009
Messages
31
Thanks for your help I worked it out with the Following Code a Advance Filter Worked.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Filter").Range("B2").Value = CStr(CBX_ProductList.Value) 'Linked Combobox1 to Cell Filter!B2
Sheets("Print Bag Flags").CBX_BagNo.Value = "Select a Bag Number" 'Tells operator to select the Bag number
Sheets("Bag Info Data").Activate 'Make sheet activate
Sheets("Bag Info Data").Range("G3,H3").Select 'then select this range
Selection.Copy ' Copy's the range
Sheets("Filter").Select ' Select the Worksheet Filter
Sheets("Filter").Range("B4:C4").PasteSpecial ' Paste the Copied range above to this range on filter worksheet
Sheets("Filter").Columns("B:B").EntireColumn.AutoFit ' Adjust the column to fit the data pasted
Sheets("Filter").Columns("C:C").EntireColumn.AutoFit ' Adjust the column to fit the data pasted
Sheets("Filter").Range("D4").Select ' Select a random cell
Sheets("Bag Info Data").Select ' Select the Worksheet Bag info Data
Application.CutCopyMode = False ' turn off the select copy cells
Sheets("Bag Info Data").Range("G4").Select 'Select a Random Cell
Sheets("Filter").Select ' Select the Worksheet Filter
' Preforms the Advance Filter
Sheets("Bag Info Data").Range("G3:H1500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Filter").Range("B1:B2"), CopyToRange:=Sheets("Filter").Range("B4:C4"), Unique:=False
Sheets("Print Bag Flags").Activate ' Select this worksheet
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Well done.

However, you don't need to Activate sheets and Ranges, especially random cells. I can't test this but seeif it works

Code:
Option Explicit


Sub x()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False


        Sheets("Filter").Range("B2").Value = CStr(CBX_ProductList.Value)    'Linked Combobox1 to Cell Filter!B2
        Sheets("Print Bag Flags").CBX_BagNo.Value = "Select a Bag Number"    'Tells operator to select the Bag number
        Sheets("Filter").Select    ' Select the Worksheet Filter
        Sheets("Filter").Range("B4:C4").Value = Sheets("Bag Info Data").Range("G3,H3").Value
        Sheets("Filter").Columns("B:C").EntireColumn.AutoFit    ' Adjust the column to fit the data pasted


        .CutCopyMode = False    ' turn off the select copy cells


        ' Preforms the Advance Filter
        Sheets("Bag Info Data").Range("G3").CurrentRegion.AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Sheets("Filter").Range("B1:B2"), _
            CopyToRange:=Sheets("Filter").Range("B4:C4"), Unique:=False


        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,646
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top