+ Reply to Thread
Results 1 to 25 of 25

Dropdown List from another Workbook

  1. #1
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Dropdown List from another Workbook

    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' )

  2. #2
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    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

  3. #3
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    447

    Re: Dropdown List from another Workbook

    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.
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  4. #4
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    439

    Re: Dropdown List from another Workbook

    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
    Last edited by zzzeddy; 12-31-2019 at 07:45 AM.

  5. #5
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

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

  6. #6
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    Quote Originally Posted by zzzeddy View Post
    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.

  7. #7
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    Quote Originally Posted by saravnepali View Post
    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

  8. #8
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    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.

  9. #9
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    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
    Last edited by AliGW; 01-02-2020 at 04:06 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    447

    Re: Dropdown List from another Workbook

    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.

  11. #11
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    Quote Originally Posted by sigit21 View Post
    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

  12. #12
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    Quote Originally Posted by saravnepali View Post
    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
    Last edited by AliGW; 01-02-2020 at 04:07 AM. Reason: Please don't quote unnecessarily!

  13. #13
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    Quote Originally Posted by MX5_Craig View Post
    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.

  14. #14
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    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?

  15. #15
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    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...

  16. #16
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: Dropdown List from another Workbook

    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.

  17. #17
    Registered User
    Join Date
    09-06-2018
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    18

    Re: Dropdown List from another Workbook

    Quote Originally Posted by hrlngrv View Post
    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'

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Dropdown List from another Workbook

    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.
    Last edited by jindon; 01-02-2020 at 03:35 AM.

  19. #19
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    Quote Originally Posted by hrlngrv View Post
    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...

  20. #20
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: Dropdown List from another Workbook

    Quote Originally Posted by MX5_Craig View Post
    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.

  21. #21
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    Quote Originally Posted by hrlngrv View Post
    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

  22. #22
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: Dropdown List from another Workbook

    Quote Originally Posted by MX5_Craig View Post
    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.

  23. #23
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Dropdown List from another Workbook

    Quote Originally Posted by hrlngrv View Post
    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

  24. #24
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: Dropdown List from another Workbook

    Quote Originally Posted by MX5_Craig View Post
    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.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Dropdown List from another Workbook

    Quote Originally Posted by sigit21 View Post
    Yes I'm sure the both file are open
    My code in #18 requires DATABASE_MATERIAL.xlsx needs to be closed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data come from another workbook using Dropdown List.
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-30-2017, 03:31 AM
  2. [SOLVED] Need password for dropdown list in protected workbook
    By abenny11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2015, 02:52 PM
  3. Dropdown list with value from another workbook
    By purin0c0 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2012, 01:21 PM
  4. Use dropdown list to call worksheets in a workbook
    By shoro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2012, 09:45 PM
  5. Dropdown list based workbook.
    By APWalker in forum Excel General
    Replies: 7
    Last Post: 07-13-2010, 01:29 AM
  6. Dropdown list connected to another workbook
    By Pieman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2006, 05:40 PM
  7. Replies: 1
    Last Post: 06-29-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1