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!
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!