Data validation list default value disappears!

This is driving me nuts! I have a large spreadsheet with many cells that have a drop down list using data validation. The list is a named range with no blanks. Most of the cells show the default I have set. However, sometimes when changes occur in cells next to one with a drop down list, the default value mysteriously dissappears and the cell appears blank. The functioning of the drop down lists is intact, it's just that the cell appears blank. I'm afraid this will confuse users. I cannot figure out why this quirky thing is happening. Any ideas?

Thanks, I appreciate the offer of help. I'm attaching it. I have narrowed it down to the worksheet change events. When they are disabled, the issue with the top value on the validation list dssappearing does not happen. The password is "mypassword"

Your code seems rather complicated but I don't completely understand what you're trying to do. You have many, many Subs that do similar but not quite the same things, and I think it is tangling you up a bit.

Let's take a case study where the user updates the data in N11, and enters a 0. When ChangeEvent4 is called, it finds that Target is in the range N3:R313 so continues. The Target value is something other than "No Data", so then it selects Target and calls ChangeData1. See my note below.

Please Login or Register  to view this content.

Also, in ChangeEvent4, you have this inexplicable line of code, which might be part of the problem:

Please Login or Register  to view this content.

This condition will never be true, because 1 = 0 will never be true. I can't figure out what you intended to do here. If you are trying to test for an odd column then you want

Please Login or Register  to view this content.

I see a few problems in the code structure. The first thing I will suggest is you nearly never use Select and act on Selection. It is more reliable to act directly on the desired range, for two reasons. First, changes in code that change what is selected and where it is selected can cause unpredictable bugs that are hard to trace. Second, selection causes activity visible to the user and slows down execution. So for example, I would recode the code above as

Please Login or Register  to view this content.

Thanks for all your suggestions. It has taken me a while to go through and apply. The source of the trouble was that I THOUGHT my macro was specifying it should only fire in odd columns, but as you pointed out that particular line of code was meaningless, and therefore the macro was firing in the even columns as well. Now that I've substituted the line as you suggested, it truly does only fire for the odd columns and the adjacent cells no longer clear.

I do indeed have many, many similar subs. They do all have a purpose. I couldn't attach the entire spreadsheet; it is too large to upload. So what you saw was much much smaller version, but big enough to demonstrate the problem.

Thanks also for the suggestion on rewriting the "changedata" code. I see your point; and that does work. Though, the code I have seems to be running quicklyl and without bugs, so 'm not sure it's worth the time to rewrite that code in the many places I would need to.

Thanks again!

Glad that I could help. I agree that once your code is complete and working there isn't a lot of value in making the changes I mentioned. However, in applications that are likely to require maintenance or new features over time, it is often worth the effort. Yours may not be one of those.