Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dropdown list not working when external file closed

0

Hello, I have a Variables file used by more than one workbook, centralised data aimed at not duplicate same data. When the Variables file is open everything works fine, when is not the DataValidation Dropdown list does not work. 
The Dropdown is defined by a Named range as follows:

'C:\Users\....\Vars.xlsx]Vars'!$A$2:INDEX('C:\Users\.....\Vars.xlsx]Vars'!$A$2:$A$200,SUMPRODUCT(--('C:\Users\.....\[Vars.xlsx]Vars'!$A$2:$A$200<>0)))

The INDEX/SUMPRODUCT is aimed at suppressing zeroes at the bottom of the list. 

All options in the Trust Centre are to allow links etc. I have also thought to run a macro that opens the Variables file when the workbook is open... i.e. I am at a loss as to find a viable solution. 

Any suggestion? Thank you. Michael

exceldata-validationnames
MS Office Version: 2019
posted by: Michael_Php (rep: 4) on Thu Feb 13, 2020 at 4:01 am
Answer
Discuss

Answers

0

You might load the Vars.xlsx workbook by including it in Excel's Startup folder. To my surprise, there seems to be no list of file locations in new versions of Excel anymore. The easiest way now seems to be via VBA.

Press Alt+F11 to open the VB Editor. In the Immediate window at the bottom of the editor's screen type ? Application.StartupPath [Enter]. Save your workbook to the folder that will be indicated there. The workbook will then be opened automatically whenever Excel is loaded.

There are ways to make the workbook load invisibly, to protect it from unauthorised modification, to make it load with particular workbooks only and to include user defined functions in it. All of these require the workbook to be loaded as add-in. I suggest you try the Startup option first and see how well it satisfies you. If your requirements grow come back here with another question.

posted by: Variatus (rep: 4889) on Thu Feb 13, 2020 at 6:05 am
Discuss

Discussion

Thanks for replying. Maybe I'm a bit thick but typing Application.StartupPath in the Immediate window at the bottom of VB editor and hitting Enter displays this error "Compile error: Invalid use of property". What am I doing worng? 
I might add that if I type a valid value - as the dropdown doesn't work - it retrieves all values pulled with a VLOOKUP, so the issue is just with the dropdown 
Michael_Php (rep: 4) Feb 13, '20 at 6:47 am
As I said, I'm not familiar with this and therefore followed this site's advice. It works on my Excel 2013 and I presume you have have had a typo in your attempt. If the error persists, however, please look at the VB Editor's Tools > References  and make sure that "Visual Basic for Applications" and an "Microsoft Excel xx.x Object Library" are checked. I expect these to be default settings but perhaps they aren't.
Here is more information and an alternative way of finding the Startup folder.
Variatus (rep: 4889) Feb 13, '20 at 8:14 am
All right thanks
Michael_Php (rep: 4) Feb 13, '20 at 8:26 am
Please let us in on your experience with having your Variables workbook in the StartUp folder. I'm also interested in if and how you got your VB to work.
Variatus (rep: 4889) Feb 13, '20 at 10:05 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login