Data Validation for Copy-Paste value

[SamarthSalunkhe]

Hi all,

I am using Data validation for the maximum value allowed, but unfortunately, it doesn't work when users do copy-past.

can anyone help me, how can I set Data Validation for copy-past also?

Thank you in advance.

 

[Joe4]

Yes, copy/paste will overwrite the Data Validation in that cell.

Your options would be to do something like:
- use VBA to validate those cells
or
- disable copy/paste

 

[SamarthSalunkhe]

Yes, copy/paste will overwrite the Data Validation in that cell.

Your options would be to do something like:
- use VBA to validate those cells
or
- disable copy/paste

Hi Joe,

Thank you,

I don't want to disable copy-past, can you please help me with the code?

I want to validate a maximum of 35 characters for a column.

 

[Joe4]

Which column (or range) exactly should have this limitation?
And what do you want to happen if they exceed those 35 characters?
Do you want to just cut it off at 35 characters, or remove the whole entry and tell them they have to try again?

 

[SamarthSalunkhe]

Which column (or range) exactly should have this limitation?
And what do you want to happen if they exceed those 35 characters?
Do you want to just cut it off at 35 characters, or remove the whole entry and tell them they have to try again?

I want to set a limit of 35 characters per cell in column A.

it should give a warning and remove the additional character from the cell.

 

[Joe4]

OK,
1. Go to the sheet you want to apply this to
2. Go to the sheet tab name at the bottom of the screen
4. Right-click and select "View Code"
5. Paste the code below in the VB Editor window that pops-up

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
    
'   See if anything entered/copied into column A
    Set Rng = Intersect(Target, Range("A:A"))
    
'   Exit if nothing put in watched column
    If Rng Is Nothing Then Exit Sub
    
'   Loop through updated values in watched range
    For Each cell In Rng
'       See if length exceeds 35
        If Len(cell) > 35 Then
            Application.EnableEvents = False
            cell.Value = Left(cell, 35)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & cell.Address(0, 0) & " limited to 35 characters", _
                vbOKOnly, "WARNING!"
        End If
    Next cell
    
End Sub

Now, whenever you type or copy/paste anything in column A over 35 characters long, it will automatically trim it and pop-up a warning message (as long as VBA/Macros are enabled).

 

[SamarthSalunkhe]

OK,
1. Go to the sheet you want to apply this to
2. Go to the sheet tab name at the bottom of the screen
4. Right-click and select "View Code"
5. Paste the code below in the VB Editor window that pops-up

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
   
'   See if anything entered/copied into column A
    Set Rng = Intersect(Target, Range("A:A"))
   
'   Exit if nothing put in watched column
    If Rng Is Nothing Then Exit Sub
   
'   Loop through updated values in watched range
    For Each cell In Rng
'       See if length exceeds 35
        If Len(cell) > 35 Then
            Application.EnableEvents = False
            cell.Value = Left(cell, 35)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & cell.Address(0, 0) & " limited to 35 characters", _
                vbOKOnly, "WARNING!"
        End If
    Next cell
   
End Sub

Now, whenever you type or copy/paste anything in column A over 35 characters long, it will automatically trim it and pop-up a warning message (as long as VBA/Macros are enabled).

Thank you so much Joe,

Code is meeting my expectations.

 

[SamarthSalunkhe]

OK,
1. Go to the sheet you want to apply this to
2. Go to the sheet tab name at the bottom of the screen
4. Right-click and select "View Code"
5. Paste the code below in the VB Editor window that pops-up

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
   
'   See if anything entered/copied into column A
    Set Rng = Intersect(Target, Range("A:A"))
   
'   Exit if nothing put in watched column
    If Rng Is Nothing Then Exit Sub
   
'   Loop through updated values in watched range
    For Each cell In Rng
'       See if length exceeds 35
        If Len(cell) > 35 Then
            Application.EnableEvents = False
            cell.Value = Left(cell, 35)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & cell.Address(0, 0) & " limited to 35 characters", _
                vbOKOnly, "WARNING!"
        End If
    Next cell
   
End Sub

Now, whenever you type or copy/paste anything in column A over 35 characters long, it will automatically trim it and pop-up a warning message (as long as VBA/Macros are enabled).

Hi Joe,

sorry to disturb you again, I have updated above code for my additional requirement. it is working fine but I think so it will be easier than I have done.

Could you please check below code and suggest how can I make it simple.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
    Const PatternFilter As String = "*[!A-Z]*"
    
'   See if anything entered/copied into column A
    Set Rng = Intersect(Target, Range("A:A"))
    
'   Exit if nothing put in watched column
    If Rng Is Nothing Then Exit Sub
    
'   Loop through updated values in watched range
    For Each cell In Rng
    
'   See if length exceeds 35
        If Len(cell) > 35 Then
            Application.EnableEvents = False
            cell.Value = Left(cell, 35)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & cell.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
                
        If cell.Text Like PatternFilter Then
            MsgBox "Entry in cell " & cell.Address(0, 0) & " should be in UPPERCASE", vbOKOnly, "WARNING!"
            Application.EnableEvents = False
            cell.Value = ""
            Cancel = True
        End If
        End If
    Next cell
    
End Sub

 

[SamarthSalunkhe]

FOUN

Hi Joe,

sorry to disturb you again, I have updated above code for my additional requirement. it is working fine but I think so it will be easier than I have done.

Could you please check below code and suggest how can I make it simple.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
    Const PatternFilter As String = "*[!A-Z]*"
   
'   See if anything entered/copied into column A
    Set Rng = Intersect(Target, Range("A:A"))
   
'   Exit if nothing put in watched column
    If Rng Is Nothing Then Exit Sub
   
'   Loop through updated values in watched range
    For Each cell In Rng
   
'   See if length exceeds 35
        If Len(cell) > 35 Then
            Application.EnableEvents = False
            cell.Value = Left(cell, 35)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & cell.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
               
        If cell.Text Like PatternFilter Then
            MsgBox "Entry in cell " & cell.Address(0, 0) & " should be in UPPERCASE", vbOKOnly, "WARNING!"
            Application.EnableEvents = False
            cell.Value = ""
            Cancel = True
        End If
        End If
    Next cell
   
End Sub

got one error, code is working only when user enter more than 3 characters.

 

[Joe4]

I don't understand what you are doing. Why are you saying it is limited to 3 characters when you are checking for a length of 35?