LookUp Using Combo Boxes

[kasperblue]

Hello,

I'm hoping someone can help me with auto-populating my VBA userform.

I have one sheet called "Master" which holds data entry for customer orders. A button on this worksheet opens the userform called DataInputForm.

There are up to 40 ComboBoxes on the data entry form, the initial one is for chosing the client 'ComboBoxCLIENT' and the rest are drop downs for chosing products ordered 'ComboBoxProductA', 'ComboBoxProductB' etc.

A second sheet ("Orders") has standing orders for some clients. So in column A there are a list of Clients (that match the dropdown combobox on the userform) and in columns B to AO there are potentially up to 40 products which need to be populated into the userform if a specific client is selected.

So far I have this code, but its tripping up on the "Set rAll" line but I can't figure out why? I had planned on doing this code for each product combobox but i'm sure there is a better way of doing it?

Please Login or Register  to view this content.

Many thanks

[AlphaFrog]

You are setting a range and you define it with a steet reference
Set rAll = Worksheets("Orders").Range(Cells(1, 1), Cells(100, 41).End(xlDown))

But you're using two other cell references without a sheet reference. So they will default to the active sheet. If the active sheet is not "Orders", it errors because it cannot create a range on Orders using cells from the active sheet that is not Orders.

Please Login or Register  to view this content.

[kasperblue]

Thanks, that stops the issue on that line. Thanks for explaining the problem.

Only issue is that the whole macro freezes now and Excel stops responding, not sure why?

[AlphaFrog]

Here's an example workbook with a records editor Userform. It's not exactly the same as what you are doing, but you may find it useful as an example.

Edit Records in Excel Worksheet Data Entry Form