Runtime Error 424 generated when activating file identified in cell

[Ochimus]

Attached ORDER file has DropLists of Products and Suppliers in Cols B - C

Attached Supplier file contain PRODUCT and COST columns.(Real folder has several different supplier files).

User selects a Product and then a Supplier.

Worksheet change finds the relevant Supplier file, the Product selected in the ORDER file, and copies the Cost from Col B of the SUPPLIER into Col D of the ORDER file.

Please Login or Register  to view this content.

All solutions, suggestions and alternatives welcome as ever.

Ochimus

[JLGWhiz]

I have edited the code but it might not help very much. The line in red font is effective only if the workbook is already open. If it is not already open then it would need to be like this:

Please Login or Register  to view this content.

to open the workbook and set the object variable. Also, I note the file extension is .xls but your sample files are posted as .xlsx and .xlsm, so that might also need editing.

Please Login or Register  to view this content.

P.S. Using Range(Target.Address) is a redundant expression. Target is a range, so that is all you need when referring to that range.

[Ochimus]

JLGWhiz,

Many thanks for response and suggestions.

The original ORDER file had a macro that opened the various supplier files when it launched, so the Supplier file would have been open before the Change macro fired, but I removed that and used your amended line to open whichever Supplier file was in the Target cell.

It didn't work whilst the folder holding the files was in the Cloud.

So I copied them to the "C" drive, opened ORDERS and changed the Target.
Supplier file now opens, but setting the Worksheet as "Sheet 1 in the Supplier file" throws a 'Runtime Error 438 - Object doesn't support this property or method'.

Simply can't see where the Syntax is wrong?

Ochimus

[JLGWhiz]

I flunked mental telepathy, so you will need to post the code as you currently have it so I can take a look at it.

[Ochimus]

JLGWhiz,

With both files on the machine, not in the Cloud, it opens the Supplier file, but then fails to "set" Sheet1, so the Index/Match doesn't link to anything.

Please Login or Register  to view this content.

Ochimus

[JLGWhiz]

There was a typo in your worksheet function statement. I changed that statement to VBA equivalent and also changed Sheet1 code name to Sheets("Sheet1") sheet name. Sheet 1, Sheets(1) and Sheets("Sheet1") can be three different sheets in the same workbook because of the way Excel is designed. See if the code below will work for you.

Please Login or Register  to view this content.

[Ochimus]

JLGWhiz

Many thanks for the suggested changes, which almost "did the trick".

Thanks to your advice, the attached amended Code works perfectly, opening the correct Supplier file. posting the correct value, and closing the Supplier file at the end.

Please Login or Register  to view this content.

Can now mark this as Solved.

Ochimus

[JLGWhiz]

Glad you got it worked out,
Regards, JLG