Ben - or anyone! Comboboxes and rowsourcing

Posted by Richard on July 12, 2001 4:44 AM

Hi there,

i am extremely unadvanced, this is my first crack at creating a form or using vb at all.

i so far have 3 comboboxes

the first is a list of customers, the second and third appear when a customer in the first box.

the second box should show what that customer has purchased

i have used indirect in the rowsource, how can i get this to change relative to what has been selected in the first combobox, i would use vlookup in a spreadsheet but do not know how to do this when using vb editor.

Thanks

Richard

Posted by Ben O. on July 12, 2001 7:24 AM

Richard,

In your Combobox1_Change() procedure (the same procedure that makes the other two boxes visible) put in the code to populate Combobox2 (previous orders). To do this, first assign the value of Combobox1 to a varible:

Combobox1.Value = sCustomer

Then you can use Vlookup on sCustomer and find the text string that specifies where that customer's data is located. First define the range that will be used in the vLookup statement.

myrange = Worksheets("Customers").Range("A2:B100")

Then assign the rowsource of Combobox3 to your Vlookup statement:

Combobox2.Rowsource = Application.Worksheetfunction.Vlookup(sClient, myrange, "2", False)

I don't think you have to use INDIRECT after all, since the Rowsource properties assumes the value you pass it is a range and not the actual values to use.

I'm assuming that your customers are in column 1, and you've entered the ranges where their data can be found in column 2.

If all of your customer data is on the same list and there aren't specific areas to look for each customer's data, then the method I'm describing won't work. If that's the case, I wouldn't know how to do what you want in Excel. Access might be the better choice.

-Ben