Pasting into a cell with data validation

[Dave Punky]

Hi all,

I'm having a real issue in regards to being able to paste into a cell which supposedly has data validation on it.

Basically I have a cell with a dropdown which gives one of 3 options. If you try and type manually into this cell it's fine it throws up the error saying you can't do that. However if I paste into the cell it goes completely unchallenged and just sits there.

I also have the same issue with a cell which has data validation for date specific values, I can just copy text into it and it completely defeats the object of having data validation in the first place.

Is there any way of disabling paste into specific cells, so say A1 has validation on it then you can't paste into that cell, you either have to manually type in or make a selection?

Any help would be appreciated!

 

[Dave Punky]

I didn't, as soon as I closed/reopened excel it worked. And I've only got it set low just while I'm doing this, it's annoying me asking to enable macros every time I open a worksheet lol.

So as we know that does actually work, what do I need to do in my sheet to replicate it as I must be doing something wrong somewhere...

 

[shg]

Can you follow the directions in my last post, and copy the code from the sheet module and code module?

In particular, you need to create the named range yourSheetName!rgnVal on the worksheet that identifies the validation cells.

 

[Dave Punky]

Ok I did exactly that, infact the code looks identical in both sheets except yours has a reference to an .xls I don't have (personal.xls?) - but it shouldn't matter anyway.

The problem I now have is that I can paste into any cell again without resistance - even on your sheet. I was worried that I'd actually deleted something by accident so I redownloaded it, closed all instances of Excel and reopened and I can paste in again. I cannot explain any reason why it would suddenly stop working like that unless theres some setting in Excel I need to re-enable. I went and ran the enable event script also provided earlier but no change.

This has to be the most frustrating thing I have ever tried to do, lol.

 

[shg]

Dunno. Somehow you are either disabling events or disabling macros entirely.

 

[Dave Punky]

It's got to be events as macros still work on the worksheet after pasting.

Is there any VBA I can stick in on opening the sheet to enable events every time its used, to somehow stop it being disabled (because I have no idea how I'm disabling it in the first place)?

 

[shg]

Search your code, project-wide, for EnableEvents.

 

[Dave Punky]

Ok the only instances I see of that are in your macro code in the normal module:

           Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True

No other instances of it anywhere in my code.

 

[shg]

Is the workbook being opened programmatically from another workbook?

How are you starting Excel?

I'm running out of suggestions ... maybe Detect and Repair is next.

 

[shg]

OK, this is my last suggestion: Post the workbook, stripped of sensitive information, on box.net or similar and post a link.

 

[Dave Punky]

Well here's my sheet, took me ages to remove all the data.

http://davido66.webs.com/spreadsheet111.xls

Some of the macros/validation may not work as I just did find and replace for pretty much everything, but the coding has remained intact. Hopefully you'll see what I'm missing.