Populating Combo Box2 based on a selection made from Combo Box1

Dear all

Please, I need a VBA code that will populate Combo Box2 based on a selection made from Combo Box1.

I have attached a sample file.

My Combo Boxes 1 and 2, respectively, are not on a userform. They are just on a Worksheet.

Looking at the file attached, The Combo Boxes are on Sheet1 and the Product Listings are on a different sheet named ‘Products’ (with the main Product Categories in column G and the Categpru/Description of the products in columns A and B).

I wish to use the setup eventually for the purposes of a quick look-up of prices of products.

I am aware that Data Validation would work OK. However, I wish to use Combo Boxes so I’ll be able to format/manipulate the lists in each combo box, hence I need a VBA code that will drive the combo boxes.

As I searched through forums, I came across a vba code written by royUK for a purpose that is slightly identical to mine (although used on a userform). Here is the link:

http://www.ozgrid.com/forum/showthre...t=55961&page=2

And here is the code:

Option Explicit 
 
Private Sub ComboBox1_Change() 
    Dim rng As Range 
    Dim cl As Range 
    Dim sChoice As String 
    Set rng = Range("a1", Range("a65536").End(xlUp)) 
     
    sChoice = Me.ComboBox1.Value 
    Me.ComboBox2.Clear 
    For Each cl In rng 
        If cl.Value = sChoice Then Me.ComboBox2.AddItem cl.Offset(0, 1).Text 
    Next cl 
End Sub

I use Excel 2007. I tried to adapt the code for my purpose. Unfortunately, I can’t get it to work for me. I should be very grateful for help with a vba code that will link the two combo boxes.

Alternatively, if royUK’s code above could be tweaked to drive my two combo boxes on sheet 1, that will be great and I would also be grateful.

Thanks.

Newqueen