Protect Data Validation cells from Copy Paste

HI All,

I have set data validations to many cells of my workbook, and they work great when the user inputs information into the cells, but when you copy and paste into the cells containing data validation, the cell properties are over written to where there is no longer data validation. As i understand this is what data validation is designed to do, but is there some way via VBA to make it where a user can copy past values into cells with data validation and they are still run through the validation to see if the value fits the requirement. I am very new to VBA, so please if you could paste your code and a little explanation of what it is doing, so that i can just copy and paste it into my worksheet and change the values to fit my needs.

Thanks in advance for any help,

You can use the Worksheet_Change event to capture a changed cell, then if the changed cell has validation you can manually validate the value. What kind of validation are you using?

Here is some stubbed out code to allow you to manually validate, if you can post some more details on the validation we can take it a bit further:

Please Login or Register  to view this content.

Wallyeye,

Thank you very much for your post. I have four sheets, 3 of which the user will be able to input their data and the fourth will give them a response. Sheet one is called Instructions, and this is where the user will input information in the form of dates, numbers, and text. On this sheet there are many different validations that make sure the user will not input the wrong type of answer, Example: it makes sure that they enter a number within the requirements when the program calls for a number, they are not allowed to enter some sort of text. One of the question on this sheet along with many other individual questions asks the user to insert values into sort of a table, this is not really a table, but when i lock the cells it turns into a place for them to insert values into a 2X20 space. Therefore they would like it if they could copy and paste their numbers into this table. On the second and third page they must put in about 200 values in a 10X20 space has number data validation, so it would be much more efficient for them to be able to copy and paste their values from their database into this worksheet.

Thank you again for your help

Originally Posted by wallyeye

You can use the Worksheet_Change event to capture a changed cell, then if the changed cell has validation you can manually validate the value. What kind of validation are you using?

Here is some stubbed out code to allow you to manually validate, if you can post some more details on the validation we can take it a bit further:

Wallyeye,

I have tried your code and it still allows the user to input the data manually, one cell at a time, with my validations. Although it still does not allow the user to copy and past values into the respective cells. I am looking for something that will allow the user to paste values into the cells that are unprotected, and for the program to recognize the pasted values and run my validations on them to check if they match the criteria. Do you happen to know of anything more that can help me?

Thanks

This version will check for numerics:

Please Login or Register  to view this content.

Name the range of all numeric validation entries "ValidationRange", and paste this code behind your worksheet. It should be cleaned up a bit for proper encapsulation, but we can do that later if it works for you.

Essentially what it does is check if ValidationRange no longer has all validations intact then it checks each cell in the changed range for numerics. If any of the pasted cells are non-numeric, it will trigger the bolValid flag and reject all entries. If all pasted cells are numeric, it will set put the values into the pasted cells, without wiping out the validation.

Wallyeye,

Your code worked perfectly for the numeric data validations. In some of the cells on my workbook my numeric data validations also have conditional validations. For example, say you have in cells A10:A41 the days in a month, 1-31, then in A1 you ask the user what day of the month it is, based on what they input in A1, cells B10:B41 have data validation that say the user can only input a number on or after the day that they put in A1. I am just thinking, because i don't know how to write VBA nor know what it is capable of, is there a way to make the data that is copied in go through the validation and not just erase it when you copy the contents of a cell into another. Is there some sort of copy past method like paste values that would work that i could create a macro to only copy in the numbers without skipping the data validation? That may not be posiible and like you are doing there may be a work around and it may take many steps, but i thank you again for all of your help.

I don't know of a way to get around the Undo. In theory, you can use the object model to read the data validation and replicate it in code, I don't know that I'm ready to do that here as it would take a bit of delving. I've created validation routines for userforms before that associate text boxes with a validation type, then just validate against that type (useful when there are 200+ text boxes). Something similar could be done here, identify the cells that use a particular type of validation (numeric only, date ranges, allowed ranges). The ValidValue routine would then be extended to check which type of validation is used for each target cell and perform the desired validation. Something like:

Please Login or Register  to view this content.

Then define a your validation set. I put mine on a different sheet, looking something like this:

Min Max Ranges
Numeric 1 1 31 B2:B4
Numeric 2 0 100 B5:B8
Numeric 3 0 1000 B9:B12
Date 1 4/9/2012 5/9/2012 B13:B14

With column A being "Numeric" or "Date", an identifer (1-3) in column B, the minimum value in column C maximum value in column D and the range to apply the validation type to in column E. For my dates, I just used =Today()-30 and =Today() to keep the range dynamic. The Ranges column can be of the common methods for spelling out ranges, including commas and colons:

B5,B6,B10,C18:C20

would work just fine. I then created the named range "ValidTypes" for A2:E5.

Actually using this, you could just remove the data validation and tweak the worksheet_change event to call this instead. There are some optimizations that could be done, if you run into performance issues.

Here is my cross post, i figured i could reach a larger audience and more guru's with posting on two large forums. Any help is greatly appretiated. Thanks,
http://www.mrexcel.com/forum/showthr...59#post3147259

Wallyeye,

Could you attach your sample workbook illustrating what you have done in your last post. I am not sure where to put everything for it too work. If i could look at an example i can most likely tweek it to fit my needs. Thank you again for all of your suggestions and help. This looks like it may be a promising solution to my problem, i will just have to make it fit my workbook. I thank you again for all of your time.

Here is my worksheet:

Okay, I see what it is doing. I am going to try and mess around with it to fit my programs needs, i think this will work for me. Thank you so much for all of your work!