I have a problem when i pasted over a value from another worksheet to the worksheet which has data validation appplied on all
For example, I want to copy the values from 'Sheet 3' to 'Sheet 1'.
Sheet 1 has the data validation with the condition such that a text length which doesn't have 9 digits will prompt an error
message ' Pls enter 9 digits'.
There is a similar macro which i think can be used for this too. But im not too
sure where i should edit to change to the condition that i want.
Dim validationCells As Range
Dim oneCell As Range
Dim userInput As Variant, promptStr As String
On Error Resume Next
Set validationCells = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not (validationCells Is Nothing) Then
For Each oneCell In validationCells
If Not (oneCell.Validation.Value) Then
promptStr = oneCell.Address & " has a bad value in it." & vbCrLf _
& "Please enter the correct data per" & vbCrLf _
userInput = Application.InputBox(prompt:=promptStr, Default:=oneCell.Value)
If userInput <> False And oneCell.Value <> vbNullString Then oneCell.Value = userInput
When i try using the data validation on sheet 1 and do a normal data entry it works fine. Just have a problem with copying
and pasting part. Any help will be much appreciated. Thanks.