How to prevent pasting the wrong values in a cell which already have a data validation?

Hi Team,

I have one sheet which has one column named as Resource Designation and it has drop-down with three values to select Manager,Clerical and Professional. I had put this using Data Validation , selecting the type as list and also put an error message if wrong value is entered b user.

My problem is , if user is pasting the values from an other excel sheet , it is taking wrong values like "Management" , data validation is not working when the user is copy pasting the values.

Please let me know the solution , i will be grateful to you all .

I will attach the sample sheet with the same data validation but not the original sheet .



Thanks
Timmu

Hi Tony,
Thanks!!

This is working fine when we are copying into the data validation cell from the same sheet like as you have written Clerk beside that data validation cell.

But when we are copying the same Clerk from different excel workbook , it is not showing up any error.

Is there any solution for this?

Please let me know

Thanks
Timmu

Hi Timmu
I have posted a question on the forum that the code originally came from and if I get an answere I will come back to you.
Thanks
Tony

Hi Tony,

I got one reply from you to my mail saying

Hi
The only way to prevent data in cells being pasted is to override the built in Ctrl+V paste option with a macro that tells the user they cannot use paste on this worksheet as it would remove the data validation rules. See my sample attached. The Macro has been given a shortcut name of Ctrl+V which overfrides the muilt in paste function of Excel. Try copying some data into Col A.
Hope this helps
Tony


can you please provide me that sample?

Hi ,

Any progress for my question above , please let me know.

Thanks
Timmu

Hi Timmu
Sorry, here is a very simple Macro which over-rides the Ctrl V option in MS Word:
Sub PreventPaste()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+v
'
MsgBox ("You cannot paste data into this workbook...")
Range("A1").Select
End Sub
Paste this into a module in your Workbook.
This will only stop them using Ctrl V to paste data. They will still be able to use the paste option on the Home toolbar (unless you remove it from the toolbar). If you do this it will affect all documents created under that template.
Hope this helps.
Tony

Hi Tony,

Thanks for the reply. I have one more issue , is it possible to validate the wrongly pasted values in the drop-down containing data Validation?

For example in my case , i have a drop-down for A1 with Data Validation having List of 3 values Manager,Professional , Clerical. If i paste say Management from a different sheet into the sheet containing A1 , it should pop-up the message saying "you have entered the incorrect value , please check" .

Is it possible to handle?

Please help

Thanks
Mounika

Pl see the attached file. It may be helpful.

Hi kvsrinivasamurthy,

You are really great , awesome , no words for you salute to you heart-fully!!


Thanks

Thanks for the compliments.Pl mark the thread SOLVED. Use Thread Tools.

Hi Mounika
Replace all code behind the Worksheet with this code and it should do the trick:

Please Login or Register  to view this content.

Hope this helps.
Good luck.
Tony

Hi Srinivasamurthy,

In the solution you provided , how to give the dynamic range? Actually we have 55 resources (rows) but we can add extra resources through one button(assigned one macro to it) . In the code you provided I added Range till 55 but when we add extra resources then the code will not work as i gave till 55 only , next will be 56 and for 56 this code will not work. How to handle this situation , hope you understood my problem

If Not Intersect(Target, Range("E10:E55")) Is Nothing Then
k = ActiveCell.Validation.Type
m = WorksheetFunction.CountIf(Val_rng, Target.Value)


Thanks again
Mounika

Try changing the range "E10:E55" as "E10:E65"

Thanks very much , it solved my problem.