Avoid pasting of values from another workbook into cells of the sheet containing the Data Validation

[Mounika]

Hi Team,

I have an FTE Template used for entering the hours of all full time employees . In that template we have one column called Resource Designation. I have a put a data validation for this with the list of 3 values Manager,Clerical and Professional.

Clients are facing one issue , whenever users are copy/pasting the values from another excel sheet into this template, data validation is not happening and its taking the wrong values like Management instead of Manager also as correct value.

Is there any way to prevent this pasting from another workbook so that data validation works fine.

I am attaching a sample file fte.xlsm but not the original file.

Please help me in resolving this issue.

 

[Luke M]

Requires some VB, but it can be (mostly) done.
http://www.j-walk.com/ss/excel/tips/tip98.htm

 

[Mounika]

Hi ,

Actually i tried this one also, but this will only avoid pasting the values within the sheet , but how about if the value is copied from one sheet and pasted in different sheet. I am attaching the sheet which will avoid copy/pasting the values within the sheet , but if you copy from a different workbook and paste in this sheet it will not work.

Please help.


Thanks
Mounika

 

[Luke M]

I don't follow...in the uploaded workbook, if I copy and paste from another sheet, the validation is either still there (wasn't going to overwrite the validation) or it's prevented. I'm not able to create a situation where DV is destroyed.

 

[Abhijeet]

Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

This Workbook :

VB:

Option Explicit
Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

 

[Mounika]

Hi ,

Can you please let me know where to write the above code mentioned . Where to write the first part and second part i think need to write in this workbook , what about the first part , please let me know asap. Thanks for the help.


Thanks
Mounika

 

[Mounika]

Sorry , i got it and its working fine but I want a solution for which pasting should be allowed and if we paste wrong values in the data validation dropdown , i should get the message is it possible ?

 

[Abhijeet]

This is in Data validation Option but if u copy paste value in Edit mode then it allow to paste that value.You can use Combo Box instead of Data validation

 

[Luke M]

Hi Mounika,

The code I linked to before should be doing what you requested. What is going wrong on your end? With your uploaded workbook, I am not able to paste over the data validation.