Data Validation issue - stops on close

[Homeslice01]

Hi all,

I got some help with a data validation issue yesterday. See link:

http://www.excelforum.com/excel-gene...alidation.html

Original file that I adapted is here.

http://www.excelforum.com/attachment...-sample-1-.xls

The problem I'm having is that the data validation - which references named ranges - stops when the workbook is closed. I open the file again and the drop down lists stop working. I go back into data validation and the formulas are all still there, then the drop down boxes start working again.

Is there any reason why this would happen? I'm trying to avoid having to go into data vaildation every time I open the workbook.

I'm using 2010 - it's possible that the document will get used on a machine running 2007. The named ranges are all on this file.

Cheers,
Rob

[Homeslice01]

Hi again,

Spent a few hours going through the possible reasons for the problem. It was all going along fine and I thought I was making it up until I enabled macros, then the data validation stopped working.

I tried it on the original file in the previous post - saved as macro enabled workbook - and the data validation stopped loading automatically. Anyone know if it's possible to use macros and this type of data validation or am I doomed to one or the other?

[humdingaling]

try these suggestions out
http://www.contextures.com/xlDataVal...rowsNotVisible

sounds like

Corruption
If none of the above solutions explains the missing dropdown arrows, the worksheet may be corrupted

[MarvinP]

Are you trying to use a Data Validation list from a closed workbook? I don't think Excel would know anything about a closed file.

If that isn't the problem then bump this post.

Read the first few sentences of:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

[Homeslice01]

Thanks again humdingaling...

I'm at work running IE which crashes everytime it needs to think about anything. I'll check the website out at home.

I should clarify, the arrows are definately there. They just don't do anything until I click on data validation. The formula is all there. I click ok and it starts working again.

It's pretty annoying. I don't think the file is corrupted because I've tried using this named range and DV on a few worksheets. They all failed when saved as macro enabled.

Cheers anyway.

[Homeslice01]

Thanks MarvinP,

I'm not clever enough to use a DV list from a closed file.

Not to waste anyone's time but if you have 2 minutes....

http://www.excelforum.com/attachment...-sample-1-.xls

Save this file (presumably it works) - open file - save as macro-enabled - close file - open file

Do the drop down arrows still work after that?

[humdingaling]

hmm i managed to replicate it
i really stumped on this one...just feels like a straight out bug

attached example for anything following

B2:b32 all have DV associated but opening opening file the arrow is there but not drop down

[MarvinP]

Hi,

It looks like the DV is using a type of Dynamic Named Range to determine what to show. If you go to the formula of the DV it shows a formula of:
=OFFSET(name,MATCH(LEFT(B2,LEN(B2)),LEFT(names,LEN(B2)),0),0,SUMPRODUCT(--(LEFT(names,LEN(B2))=B2)),1)

Does that answer the question? It looks like the formula above uses the Normal Named Range (name) to calculate what to show.

[humdingaling]

MarvinP - download the original file in post #1

when you open that one all the DVs in column b work as intended without having to go into DV to "reactivate" the DV

the query then is what does MACRO enabled workbook do to the file to make it stop working like the original file

[MarvinP]

There is a formula in the DV as shown in post 8.
There are also named ranges with formulas shown in the names manager.

I wonder if the problem might be that "Name" is a reserved word in VBA. Change the range names away from "name" and "names" and see what happens.

I guess I trust Excel to do things correctly and try to change my thinking to match what it does. I just can't get my head around what the OP expects it to do, or not do in this thread.

[Homeslice01]

Hi Marvin,

Thanks for the help.

The formula in post 8 works when the document is a .xls file. If you save as an .xlsm file (as in post 7) the DV stops working when you open the file despite all the formulas still being there. You then have to click on Data Validation to get it to work. Something happens when the file format is changed from an .xls to a .xlsm that I just don't understand.

I guess I could double down on the macro enable content and write a macro that automatically clicks on the data validation button on startup?

[MarvinP]

That makes a lot more sense now that you say it is an .xls vs .xlsm type problem. .xlsm are a lot more secure and ask to run a macro while the older .xls could have viruses that ran automatically.
http://www.differencebetween.net/tec...-xls-and-xlsx/ gets close to this problem but not enough detail..
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx is closer to a better answer... Read down to section of "Macro-Enabled Files vs. Macro-Free Files"

[humdingaling]

the fault replicates when saving as XLSX as well ,so its not just XLSM

im stumped, sorry homeslice
yes you can create macro to run DV on opening
you just need to place it in Workbook_Open

it does work i just tried it on the sample file

[Homeslice01]

Yes it does seem like I lucked out here.

I must say it's amazing that a bunch of strangers would put in the time to help me out. You guys are awesome!

Humdingaling, I'm pretty rubbish at writing macros too. If you could swing me the solution, I'd be grateful.

Merci.

[humdingaling]

sorry back from holidays

attached is example of how to "automatically" apply DV on workbook opening

the code i got just from recording the application of DV and amending slightly
may not be the most optimised but it will work

[Homeslice01]

Hiya Humdinaling,

Hope your holiday was great. I thought you'd forgotten this and only checked the thread by chance.

Just updated the security settings and this works fine. Thanks. You're a champ.

[humdingaling]

not a problem, i usually stay subscribed to threads for quite sometime

glad it works for you
if the problem has been solved please closed the thread by marking it solved


Cheers
Humdinga