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!

 

[ziad alsayed]

dear all

i am a bit confused here, what is the final code , i am interesting in having it.

thanks in advance

 

[Dave Punky]

dear all

i am a bit confused here, what is the final code , i am interesting in having it.

thanks in advance

We've not got quite that far yet but the codes previously noted should in theory work on other spreadsheets...

 

[shg]

OK, two things:

rgnVal should be defined with Worksheet scope:

Sps1!rgnVal refers to: ~ (the definition is fine (didn't look closely), the name is not)

Then delete the routine SuperPaste

 

[Dave Punky]

The only annoying thing with that is SuperPaste was the only thing I could find/work out to stop pasting screwing up any conditional formatting in place in a cell on the sheet. I never got an official answer on that in my original post on it. I've deleted it anyway.

And I've defined Sps1!rgnVal under the following line (and changed the name as I know I had VAL in caps):

    On Error Resume Next
    Set rChk = Intersect(r, r.Worksheet.Names("Sps1!rgnVal").RefersToRange)

However even with those changes it's exactly the same, can still paste into those cells without challenge. You sure none of the other scripts could be causing the problem or have I missed something again when correcting your script?

 

[shg]

When code, like your SuperPaste, modifies a worksheet, it flushes the undo stack, Dave.

And I've defined Sps1!rgnVal under the following line ...

That's not what I suggested -- please don't change the code. One sheet Sps1,

Insert > Name > Define, Sps1!rgnVal

I deleted SuperPaste and corrected the definition of rgnVal -- that's all -- and it worked fine.

 

[Dave Punky]

Sorry my bad, misunderstanding on my part. Corrected that back to the original code.

I've done exactly as you specified and again it's not working. Are you able to upload your version of the spreadsheet where it works so I can see for myself, as it won't let me define the name as Sps1!rgnVal, rather just rgnVal on Sps1.

 

[shg]

http://www.box.net/shared/lggfpa23qp

I hard-coded the validation range in the worksheet change event. Fending off all of the other code in the workbook was getting too difficult.

 

[Dave Punky]

Ok I think maybe there's something wrong with my excel because I can still paste into those ranges on your version of the sheet without issue - despite the fact I can clearly see how it works and there is simply no logical reason why it shouldn't work.

Just for my sanity, is there any way of creating a formula assigned to a macro so when assigned to Ctrl & V, it just activates this macro or doesn't paste into those cells? I'm gonna test that sheet on another machine too just to make sure it's not just this machine.