Dropdown List from another Workbook

[sigit21]

Hi guys, I got a problem when trying to create a dropdown list using data from a different workbook. It works well when the data is in the same workbook. Down below is the code that gave me error 1004:

Please Login or Register  to view this content.

What I'm trying to do with the code above is Create a dropdown list on cells C12:C21, using the data from file "DATABASE_MATERIAL.xlsx" worksheet "BB" cells "D2:D20".
Please help me with this guys, I've been googling but found nothing about this problem. Thanks' )

[MX5_Craig]

I'd like to help you, but I don't quite understand what you are trying to do. Could you provide a bit more detail, I could show you how to populate a combobox like this example www . excel-easy.com/vba/examples/combo-box.html using your database

[saravnepali]

You are NOT allowed to use [ ] on the refreence name.

And there is a wa around to make it happen BUT NOT RECOMMENDED as both workbook have to be open for it to work.

[zzzeddy]

Hi sigi21

You could just copy-and-paste that external data list into your workbook, and then refer to that.
Or you could use link-formulas e.g. make sure your DATABASE_MATERIAL.xlsx file is open, then, in your other workbook..
1 add a new sheet
2. in cell [A2], enter
=[DATABASE_MATERIAL.xlsx]BB'!D2
..and copy down to row 20
..then use..
formulatext = "=SheetX!$A$2:$A$20"
..where SheetX is the new sheet you added.

Or, if you don't want to use links in your workbook (see post above), you could use the Workbook_Open event to 'import' that source list etc etc etc

zeddy

[sigit21]

What I'm going to do is to create a dropdown in a cell without using a combobox.

[sigit21]

Hi sigi21

You could just copy-and-paste that external data list into your workbook, and then refer to that.
Or you could use link-formulas e.g. make sure your DATABASE_MATERIAL.xlsx file is open, then, in your other workbook..
1 add a new sheet
2. in cell [A2], enter
=[DATABASE_MATERIAL.xlsx]BB'!D2
..and copy down to row 20
..then use..
formulatext = "=SheetX!$A$2:$A$20"
..where SheetX is the new sheet you added.

Or, if you don't want to use links in your workbook (see post above), you could use the Workbook_Open event to 'import' that source list etc etc etc

zeddy

Yess it could be done by copying the data into the workbook, but the data is quite big and have thousand of rows. So I think it will be easier if there is a way to refer it directly from the other workbook.

[sigit21]

You are NOT allowed to use [ ] on the refreence name.

And there is a wa around to make it happen BUT NOT RECOMMENDED as both workbook have to be open for it to work.

What do you mean by "wa"?
If it not allowed to use [], then how to separate the workbook name and the worksheet name? can you give me an example?
Thank you

[MX5_Craig]

This works on my test machine. You will need to replace the name of your database at dbWBName. you will also need to replace dbSheetName with BB I think.
The database workbook must also be open. Let me know if this helps. We define a named list that refers to your database and then set that as the formula Thanks Craig

Please Login or Register  to view this content.

[sigit21]

Hi Craig, thank you for your answer. But it seems it doesn't work for me. It still gave me an error 1004. I attached the screenshot below.
Attachment 655873
Attachment 655874

[saravnepali]

You have to make sure that both files are open for this code to work (OR you might add more code to check if both files are open before running this code)

Also, the first workbook with the range has already got the range ( I have named it list) on it'

Please Login or Register  to view this content.

[MX5_Craig]

Hi Craig, thank you for your answer. But it seems it doesn't work for me. It still gave me an error 1004. I attached the screenshot below.
Attachment 655873
Attachment 655874

Can you post the code you used I will see if i can spot any issues, thanks

[sigit21]

You have to make sure that both files are open for this code to work

Yes I'm sure the both file are open, it still won't work

[sigit21]

Can you post the code you used I will see if i can spot any issues, thanks

Here the code I used:

Please Login or Register  to view this content.

[MX5_Craig]

How strange that it doesn't work, your replacements all look fine, provided that you have spelt everything correctly and the sheet on the external workbook is called BB. Have you tried putting simple numbers in your DATABASE_MATERIAL.xlsx rows D2:D20? As that is what I tested with.

Can you confirm that the nm variable is being set (by placing a breakpoint on that line of code) Does the debug line occur when trying to .add the list?

[sigit21]

Yes the debug line still occur in validation.add. I think I will do zzzeddy suggestion by using a link-formula from the database_material.xlsx and create a reference worksheet in my main workbook, it's the only way that works.
Thank you so much for your help Craig...

[hrlngrv]

Excel doesn't allow external references to be used a data validation lists. If the workbook D:\DATABASE_MATERIAL.xlsx were open, you could use defined names referring to ranges in it to get around this limitation, but that'd cease to work once you close that workbook. There's no robust way to use external references in data validation.

[sigit21]

Excel doesn't allow external references to be used a data validation lists. If the workbook D:\DATABASE_MATERIAL.xlsx were open, you could use defined names referring to ranges in it to get around this limitation, but that'd cease to work once you close that workbook. There's no robust way to use external references in data validation.

Ohh I see, that's why it couldn't work when I try to do so. Thanks'

[jindon]

To ThisWorkbook Code module.
Assuming Sheet1 is the name to set the validation.

Please Login or Register  to view this content.

Save the workbook and open it again.

[MX5_Craig]

Excel doesn't allow external references to be used a data validation lists. If the workbook D:\DATABASE_MATERIAL.xlsx were open, you could use defined names referring to ranges in it to get around this limitation, but that'd cease to work once you close that workbook. There's no robust way to use external references in data validation.

The solution I posted on page 1 uses named ranges, but apparently that is not working, but it did work on my test machine with my test data...

[hrlngrv]

The solution I posted on page 1 uses named ranges, but apparently that is not working, but it did work on my test machine with my test data...

Defined names referring to external workbooks work when those other workbooks are open, but not when they're closed. When they're open, drive and directory aren't needed, so seeing external references WITH drive and directory indicates one should try things with other workbooks closed.

[MX5_Craig]

Defined names referring to external workbooks work when those other workbooks are open, but not when they're closed. When they're open, drive and directory aren't needed, so seeing external references WITH drive and directory indicates one should try things with other workbooks closed.

Hi, this is all known the OP had both workbooks open, the code I posted does not reference drive and directories directly, go back and have another look at it

[hrlngrv]

Hi, this is all known the OP had both workbooks open, the code I posted does not reference drive and directories directly, go back and have another look at it

Robustness counts. Using defined names referring to external workbooks just isn't robust for data validation. If the other workbook's full pathname is known, it's safer to use cell formulas to pull in the needed data, then use that internal range as the data validation list. If that's what you proposed, great.

[MX5_Craig]

Robustness counts. Using defined names referring to external workbooks just isn't robust for data validation. If the other workbook's full pathname is known, it's safer to use cell formulas to pull in the needed data, then use that internal range as the data validation list. If that's what you proposed, great.

Hi, I beg to differ with your opinion, I agree that robustness counts, but I don't think keeping stale copies of formulas is good for robustness, for me personally I would, wherever possible get all data from it's source everytime it's needed (in this case the database workbook). Good robust code would ensure that the code has opened the database and reports an error if whatever reason it can't find or open the database.

What you propose is akin to saying relying on the internet to get the latest news is not robust because it relies on an active internet connection and instead we should be making copies to an offline store, and personally I disagree with this but I realise that others may think this is preferred. Thanks, Craig

[hrlngrv]

Hi, I beg to differ with your opinion, . . . Good robust code would ensure that the code has opened the database and reports an error if whatever reason it can't find or open the database.

What you propose is akin to saying relying on the internet to get the latest news is not robust because it relies on an active internet connection and instead we should be making copies to an offline store, and personally I disagree with this but I realise that others may think this is preferred. Thanks, Craig

You mischaracterize my suggestion, possibly because you don't understand it.

Formulas in cells referring to external workbooks CAN BE refreshed as often as needed. Indeed, if one used a Worksheet_SelectionChange macro to refresh database connections, one could just as easily use it to refresh external references in cell formulas. Point is, if the source data for a validation list were in another workbook, external references to that other workbook would be refreshed automatically, without any event handler needed, whenever the workbook with the external references were opened. When the other workbook was open in the same Excel session, any changes in it would immediately propagate to any workbook with external references to it via standard recalculation. Only changes to external workbook NOT OPEN in the current Excel session would require event handlers to force refresh.

I'm not saying use possibly stale values. Those are words you're trying to put in my mouth, so don't act surprised when I reject that. I believe external reference in cell formulas provide the best mix of automatic refresh and relatively simple and quick event-driven refresh.

[jindon]

Yes I'm sure the both file are open

My code in #18 requires DATABASE_MATERIAL.xlsx needs to be closed