Cell Validation Not Responding

[Ark68]

I am having a problem.
I have a validated cell that I copy from one protected worksheet to another.
At the source, the cell's list validation works. Click on the cell and the little arrow shows.
However, at the target worksheet, the user can click on the cell (which is unlocked) but their is no little arrow. Checking the cell's properties indicates that there is cell validation in place.

Any idea why I don't have the list validation function?

I can type in the selected cell, and any invalid entry (according to the validation list) will trigger the error message. So the validation list is there, just no option for the drop down.

Jenn

 

[Colin Legg]

I managed to replicate your scenario if I protected the target sheet and pasted into an unlocked cell on that sheet.

 

[Colin Legg]

Just to clarify, (in XL 2007) if you right click and choose paste special, the data validation option is greyed out if the worksheet is protected. So with protection enforced, it looks like you can't transfer data validation this way.

 

[Ark68]

Hi Colin ... happy to see you again! Well, hear from you anayway.

I am not using Excel 2007, just 2003. The target sheet I suppose is protected, and yes, if I copy and manually try to paste special, validation is greyed out.

I've removed all instances of protecting the target worksheet, but still each copy and paste fails to leave the validation arrow, whether through the code, or a manual cut/paste special all.

What I find so odd is the validation is there, any typed entry fails if not in the list. Just no drop down arrow.

Jenn

 

[jeffreybrown]

Hi Jenn,

If you want to upload your worksheet to someplace like box.net I can take a look or if you want to PM me I can send you my e-mail.

 

[Colin Legg]

Hi Colin ... happy to see you again! Well, hear from you anayway.

I am not using Excel 2007, just 2003. The target sheet I suppose is protected, and yes, if I copy and manually try to paste special, validation is greyed out.

I've removed all instances of protecting the target worksheet, but still each copy and paste fails to leave the validation arrow, whether through the code, or a manual cut/paste special all.

What I find so odd is the validation is there, any typed entry fails if not in the list. Just no drop down arrow.

Jenn

Are you copying and pasting between 2 different workbooks?

One reason the dropdown arrows could be "missing" is if objects are hidden in the target workbook. This is a workbook level setting, so I think you should double check this in the target workbook. From memory, in XL 2003 this setting is under Tools | Options | View tab, and there should be a setting to show/hide all objects. In XL 2007 it is under the "Display Options For This Workbook" section in the Advanced Options area.

 

[Ark68]

Hi guys ...

Jeffrey ... I will consider sending my project but I fear doing that will cause just as many problems as it consists of so many links to different workbooks etc. to make things work.

I think Colin ... you may be hitting the nail straight dead on the head. Using only one workbook in this scenario, and the option to display all objects is default, so nothing for me to adjust there.

Check out this thread I posted about an annoying and halting hurdle.
http://www.mrexcel.com/forum/showthread.php?t=458812

It never did get resolved fully. I simply masked the errors with on error resume etc lines. So ... Colin ... my guess is there are hidden (many several) remnents (ghostly) drop downs still lurking around in the worksheet. But, as you can see from the thread above, I have not had any success in getting rid of them.

Now I'm very scared. I am about 3 days from completing this report and I'd hate to think ghosts are preventing my success.

Jenn

 

[Colin Legg]

Sounds like there's a lot going on in this workbook... it's going to be very hard to get to the bottom of it without seeing it. But reading that other thread and seeing what you are doing - continuously adding and deleting data validation, I think I have an idea.

Each time you add a "data validation list" to a worksheet , Excel automatically increments its drop down shape number by one. So, suppose you start with a blank sheet. If you copy over some data validation (list) into it then the dropdown for that cell will be Drop Down 1. If you copy over again into another cell then that one will be Drop Down 2. If you delete them both and then copy a new one over, the new one will be called Drop Down 3. In other words, Excel remembers that you've had other ones in there. Can this numbering process keep on going for ever, or is there a limit? Well, in Excel 2003 there's a limit of ~ 65536. I reckon you've reached this limit so it can't make the next one.

I don't have XL2003 at home to test this theory (and I know the limit has for all practical purposes been removed in XL2007), so it's a bit of a guess and you'll need to be lenient with my description (which isn't as precise as I would like it to be!). You can either do some investigating of your own or send me the workbook to check (at work), it's up to you.

The reason I know this is something similar happened to me a while back with the autofilter (which is also drop down shapes) in XL 2002. The guys at XVBT should be credited with nailing the problem -
http://www.xtremevbtalk.com/showthread.php?t=296474


Hope that helps...

 

[Ark68]

LOL! My what a mess Jenn got herself into. All that testing of my code maxed me out?
Let me share the primary workbook with you Colin since you have so generously offered to have a peek. (In all honesty ... the explanations given in the trail of help you received had me spinning ... faster than usual).

I'm not sure how far you will get with the application because this workbook does contain links to data sources in other workbooks. I suppose if you find yourself making headway and the additional resources are needed, I certianly can send them as well.

Jenn

 

[Colin Legg]

LOL! My what a mess Jenn got herself into

Yep, you've gone the full hog on this on.

Like you said, it's not just when you are copying/pasting - data validation dropdowns are not showing anywhere in the sheet, even if you try to add new ones.

I had a look and checked out the usual suspects -

• Frozen panes
• Hidden objects setting

Nothing wrong with those.


I also tried a few other things -

• open+repair
• renaming the worksheet / save / close + reopen

No joy.


At that point I used google to see if anyone else has had this problem and I got this hit:
http://www.excelbanter.com/showthread.php?t=123022


I'll play around with it a little more but the way people seem to have worked around this "broken data validation dropdown functionality" is to just set up a new replacement worksheet.

 

[Colin Legg]

Instead of creating a new replacement sheet, another way I found of fixing it was to drag the worksheet into another workbook and then drag it back in again.