Validate pasted data

[Aguspwc]

Hi everyone!

I looked for this everywhere and can't find a solution that works 100%. I need to paste data from an external source, to an excel sheet. That sheet is later used with Power BI, so in order to avoid mistakes when pasting the data I want to make validations. I know you can validate cells values easily, but that only works when you input the values one by one. If you copy and paste the validations are lost. So I'm wondering how to avoid that. I found a possible solution using vb, but in this way I can only validate one column. This is a short example:

I'd like to validate, for example, the first column as just numeric type, the second just dates, the third one not let letters in, and the fourth one a max length of 4 characters. Something like this. What I got so far is this code:
This one validates the length

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Valida cantidad de caracteres
Dim ValidatedCells As Range
Dim Cell As Range
Dim Limite As Integer
Limite = 8
Set ValidatedCells = Intersect(Target, Target.Parent.Range("A2:A1048576"))
If Not ValidatedCells Is Nothing Then
For Each Cell In ValidatedCells
If Not Len(Cell.Value) <= Limite Then
MsgBox "El valor""" & Cell.Value & _
""" ingresado en " & Cell.Address & _
" en la columna A excede el límite de " & Limite & " caracteres", vbCritical
Application.Undo
Exit Sub
End If
Next Cell
End If
End Sub

And this one validates that is just numeric type:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Target
If Not Application.Intersect(cell, Range("B1:B1048576")) Is Nothing Then
If Not IsNumeric(cell.Value) Then
MsgBox "El valor""" & cell.Value & _
""" ingresado en " & cell.Address & _
" en la columna B no es de tipo numérico", vbCritical
Application.Undo
Exit Sub
End If
End If
Next cell
End Sub

They both work well, but just can use one at the time. I tried using both with a Select Case, but couldn't make it work.

Any help will be appreciated.
Thank you!

 

[FormR]

Hi, welcome to the forum!

You could try a structure similar to this..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidatedCells  As Range, Cell  As Range
Set ValidatedCells = Intersect(Target, Columns("A:D"))
If Not ValidatedCells Is Nothing Then
    For Each Cell In ValidatedCells
        Select Case Cell.Column
            Case 1 'validation for columm 1
                If Not IsNumeric(Cell.Value) Then
                    MsgBox Cell.Value & " is not numeric"
                End If
            Case 2 'validation for columm 2
                If Not (IsDate(Cell.Value)) Then
                    MsgBox Cell.Value & " is not a date"
                End If
            Case 3 'validation for columm 3
                'etc
            Case 4 'validation for columm 4
                'etc
        End Select
    Next Cell
End If
End Sub

 

[Aguspwc]

Hi, welcome to the forum!

You could try a structure similar to this..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidatedCells  As Range, Cell  As Range
Set ValidatedCells = Intersect(Target, Columns("A:D"))
If Not ValidatedCells Is Nothing Then
    For Each Cell In ValidatedCells
        Select Case Cell.Column
            Case 1 'validation for columm 1
                If Not IsNumeric(Cell.Value) Then
                    MsgBox Cell.Value & " is not numeric"
                End If
            Case 2 'validation for columm 2
                If Not (IsDate(Cell.Value)) Then
                    MsgBox Cell.Value & " is not a date"
                End If
            Case 3 'validation for columm 3
                'etc
            Case 4 'validation for columm 4
                'etc
        End Select
    Next Cell
End If
End Sub

Thank you very much! I tried it and it work pretty well. I'll try to adapt it. Thanks again!

 

[Yelnats23]

Hi, welcome to the forum!

You could try a structure similar to this..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidatedCells  As Range, Cell  As Range
Set ValidatedCells = Intersect(Target, Columns("A:D"))
If Not ValidatedCells Is Nothing Then
    For Each Cell In ValidatedCells
        Select Case Cell.Column
            Case 1 'validation for columm 1
                If Not IsNumeric(Cell.Value) Then
                    MsgBox Cell.Value & " is not numeric"
                End If
            Case 2 'validation for columm 2
                If Not (IsDate(Cell.Value)) Then
                    MsgBox Cell.Value & " is not a date"
                End If
            Case 3 'validation for columm 3
                'etc
            Case 4 'validation for columm 4
                'etc
        End Select
    Next Cell
End If
End Sub

Thanks for the guideline !