User FAQs

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.

alt text

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
        Exit Sub
    Else
        Application.Undo
        Call MsgBox("You cannot paste data over cells with Data Validation." & _
                    vbCrLf & vbCrLf & _
                    "Please use the drop-down to enter data.", vbCritical)
    End If
End Sub

Private Function AllCellsHaveValidation(rgeCells) As Boolean
    On Error Resume Next
    rgeCells = rgeCells.Validation.Type
    If Err.Number = 0 Then
       AllCellsHaveValidation = True
    Else
       AllCellsHaveValidation = False
    End If
End Function


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext