If you have a question, please send it to us.
1) Can you describe Data Validation ?
This allows you to create restrictions on the type of data that can be entered in a cell.
2) How can I stop the user pasting over data validation ?
Data validation will prevent incorrect values being typed into a cell.
If will not prevent the user from pasting an incorrect value into the cell.
You can prevent this from happening by writing a VBA macro.
This code detects when cells are changed, checks if data validation is present and stops the paste taking place.
Copy the following VBA code and paste it into the corresponding worksheet code module.
Private Sub Worksheet_Change(ByVal Target As Range)
If AllCellsHaveValidation(Range("B2:B8")) Then
Call MsgBox("You cannot paste data over cells with Data Validation." & _
vbCrLf & vbCrLf & _
"Please use the drop-down to enter data.", vbCritical)
Private Function AllCellsHaveValidation(rgeCells) As Boolean
On Error Resume Next
rgeCells = rgeCells.Validation.Type
If Err.Number = 0 Then
AllCellsHaveValidation = True
AllCellsHaveValidation = False
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext