Validate pasted data

Aguspwc

New Member
Joined
May 5, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:
ExcelEjemplo1.PNG

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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi, welcome to the forum!

You could try a structure similar to this..

VBA Code:
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
 
Upvote 0
Solution
Hi, welcome to the forum!

You could try a structure similar to this..

VBA Code:
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!
 
Upvote 0
Hi, welcome to the forum!

You could try a structure similar to this..

VBA Code:
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 !
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top