Help with an age-old data validation override problem

[aklewe]

Hi there! I have to find a solution for a problem we are having with a process at work. We have files that need updated monthly. Users copy and paste from their own excel files into a table in my files. They are supposed to CPV (copy / paste as values) numerical values only, but we invariably run into issue with someone NOT following that procedure. Last month I had to spend several extra hours combing thru the data inputs and cleaning up the random non-numerical values that were pasted incorrectly (the scale of the dataset is very large and across many input files). I have data validation in the cells that are to be updated, but of course the Paste action overrides those rules.

I have googled and googled and am not finding a solution that best fits my needs. I do not want to prevent pasting altogether as this would cause more problems than it would fix. What I am thinking about is just adding a code to "catch" when a user just "Pastes" without "Pasting as Values".... for example, on event, if user only pastes (and not CPV), undo the action and return a message stating "Please copy / paste as values."

Is that possible? I admit my VBA skills are so rusty I am not confident in my ability to write a code to do this myself.

I appreciate any help! I attached a simple file as an example. For confidentiality reasons, I can't post the actual file I am working with. The yellow cells are the cells we want users to update/paste into. All other cells are locked for editing.

 

[p45cal]

Is the problem that there are formulae being pasted or that there is text… or both?
You can experiment with the likes of:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cll In Target.Cells
  If cll.HasFormula Then
    Application.EnableEvents = False
    Application.Undo
    MsgBox "Try again"
    Application.EnableEvents = True
    Exit For
  End If
Next cll
End Sub

in the sheet's code-module, which can be made more selective according to where things are pasted.

 

[aklewe]

Thank you! Yes, we have had cases where users tried pasting both formulas or non-numerical values. This solution works great for the formulas. What would be the syntax needed to also catch text/non-numerical values?

 

[p45cal]

Have a go with:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellsToCheck As Range
Set CellsToCheck = Intersect(Range("E4:N6"), Target)
If Not CellsToCheck Is Nothing Then
  For Each cll In CellsToCheck.Cells
    If cll.HasFormula Or Not IsNumeric(cll.Value) Then
      Application.EnableEvents = False
      Application.Undo
      MsgBox "Try again"
      Application.EnableEvents = True
      Exit For
    End If
  Next cll
End If
End Sub

where I've also asked it only to check for cells changed within the range E4:N6.

 

[aklewe]

Thanks again. This does catch text, but only if the first value in the range they are pasting is text. If, for example, they are pasting across 4 months/columns, and the last one is text but the other 3 are numbers, it allows the copy/paste and thereby overrides the data validation rule. This was kind of why I was thinking of taking the approach of a way of stopping a paste only action, but allowing a CPV action. Just not sure if that's possible?

 

[p45cal]

I tested this, and even if only the last cell is the only cell of a block of cells being copied (plain copy or values copy) the paste is still rejected.
You need to suattach a file and show this failing, stating what's being copied and where to and including your current event handler code.

 

[p45cal]

At the moment, I don't know whether it's possible to stop a plain copy/paste easily. Perhaps, if the data validation isn't too complicated, we could restore data validation after pasting.

 

[aklewe]

Ok got it. Sorry about that. Attached is an updated file. I did not alter your code from above. In Sheet2, I have some test inputs that I am copy and pasting into the yellow input cells on Sheet1. When I copy Input Row B, which contains 3 columns of numbers and 4th column as text into E6 on Sheet , it is allowing the copy to occur.

 

[p45cal]

Ahh, it's a numbers stored as text. OK, try testing changing:
If cll.HasFormula Or Not IsNumeric(cll.Value) Then
to:
If cll.HasFormula Or Not Application.IsNumber(cll.Value) Then

 

[p45cal]

…or even:
If cll.HasFormula Or TypeName(cll.Value) = "String" Then

 

[p45cal]

If your range isn't ginormous, you could re-establish datavalidation; this line (still in the sheet's code-module) re-establishes data validation in your sample file:

With Range("E4:N6").Validation
  .Delete
  .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ISNUMBER(E4)"
End With

Only use after successful paste:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellsToCheck As Range
Set CellsToCheck = Intersect(Range("E4:N6"), Target)
If Not CellsToCheck Is Nothing Then
  For Each cll In CellsToCheck.Cells
    '   If cll.HasFormula Or Not IsNumeric(cll.Value) Then
    '  If cll.HasFormula Or Not Application.IsNumber(cll.Value) Then
    If cll.HasFormula Or TypeName(cll.Value) = "String" Then
      Application.EnableEvents = False
      Application.Undo
      MsgBox "Try again"
      Application.EnableEvents = True
      Exit Sub  <<<note change.
    End If
  Next cll
  With Range("E4:N6").Validation
    .Delete
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ISNUMBER(E4)"
  End With
End If
End Sub

 

[aklewe]

Thanks for this! It certainly works in my test file for all the test scenarios. My actual input files range from around 1000 to over 20,000 cells with the data validation applied. I will do some testing with a few of the larger files and see if this code negatively impacts performance (we have to post them to SharePoint for users to check-out and update).

I attached the updated test file here for posterity.

 

[Peter Bartholomew]

Just a thought. Would using Power Query for the data import be more reliable?
Other options might be allowing the tables to be uploaded to a database table and then queried for analysis but that is running ahead of my experience.