Validation of pasted cells

**ddubbs** · 06-10-2013, 05:15 PM

Hi all,

Hoping you can help me. I've been looking everywhere, and all I can find are methods to prevent users from pasting over data validation cells. That's not what I am trying to accomplish.

This is a template that needs to be filled out by 50 people. They take information from their personal spreadsheets and dump it in here. Being able to copy/paste data is imperative. I also still need data validation on certain columns because this master spreadsheet will feed many systems that require specific text.

I have attached a sample part of my spreadsheet.

test.xlsm

My only ideas:
Find code to lock paste shortcuts to special paste values only. Use another event to trigger validation of cell values. If a cell value does not match anything in the range, then change cell bgcolor to red and display error "Check data; Values must match those found on PickList sheet."

Thanks in advance for any help.

**ddubbs** · 06-11-2013, 12:22 AM

Any takers? All search results turn up old and attachments/links have expired.

I've also cross-posted this to http://www.mrexcel.com/forum/excel-q...ed-values.html.

My thought is to not enforce the data validation from the picklists, and let the user enter whatever they want. However, when they enter something that is not found on the picklists, the box turns red notifying the user that they need to fix it.

Thanks again.

**FDibbins** · 06-11-2013, 12:32 AM

Hi.

After 52 views, it doesnt seem like any-one has an answer for you (I know I dont, sorry)

1 of the big drawbacks of DV is that you can defeat it with copy/paste, so that pretty much says you cannot do what you want with regular means...unless some-one can come up with a VBA solution.

An alternative method might be to use helper columns that flag you is a certain value is entered (or not entered) into certain cells. So, to flag C7 if if contains "Pat", something like...
=IF(C7="","",IF(ISERROR(VLOOKUP(C7,$F$12:$F$15,1,0)),"","Check"))

**Hurricanefly** · 06-11-2013, 02:57 AM

If I have this right you would like to paste over Data validation and still keep the Data validation list? I have Excel 2013 and if I paste LINK over the data validation you can still use the drop down list.

**Jakobshavn** · 06-11-2013, 07:36 AM

This is not a complete solution, only a demonstration of technique (I am only guarding cell C2). I am using the worksheet Change event to provide additional "protection" to cell C2.