Data Validation for Copy-Paste value

SamarthSalunkhe

SamarthSalunkhe

Board Regular
Joined
Jun 14, 2021
Messages
103
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Sort by date Sort by votes
Joe4 said:
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?
Click to expand...
Sorry typo mistake,

I have added code to check characters are in UPPERCASE only. but it is working only when characters are more than 35.

but if characters are under 35 then UPPERCASE code is not working. I have tried my best but it doesn't work.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
SamarthSalunkhe said:
Sorry typo mistake,

I have added code to check characters are in UPPERCASE only. but it is working only when characters are more than 35.

but if characters are under 35 then UPPERCASE code is not working. I have tried my best but it doesn't work.
Click to expand...
Yep, that is because you embedded/nested your IF...THEN to check for uppercase WITHIN the IF...THEN that checks to see if the length is over 35.
So if your length is not over 35, it will NEVER hit that IF...THEN block.

Where you have two "END IF" statements in a row, remove one and place it just above the second "IF" (then one for your uppercase), and it should work.
 
Upvote 0
Joe4 said:
Yep, that is because you embedded/nested your IF...THEN to check for uppercase WITHIN the IF...THEN that checks to see if the length is over 35.
So if your length is not over 35, it will NEVER hit that IF...THEN block.

Where you have two "END IF" statements in a row, remove one and place it just above the second "IF" (then one for your uppercase), and it should work.
Click to expand...
Yes Its working now.

I am beginner in VBA that's why 😢

😍 Thank you for your support.
 
Upvote 0
You are welcome.
No worries!

I hope it makes sense why that is. If you nest/embed IF...THEN block within other IF...THEN blocks, it will only ever look at the second one if the first condition is met.
 
Upvote 0
Joe4 said:
You are welcome.
No worries!

I hope it makes sense why that is. If you nest/embed IF...THEN block within other IF...THEN blocks, it will only ever look at the second one if the first condition is met.
Click to expand...
Sorry Joe, again I am disturbing you.

I have combined below 2 codes but only one code is working 😢

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim RngB As Range
    Dim CellB As Range

    Dim RngCE As Range
    Dim CellCE As Range
    
    Const TextPatternB As String = "*[!A-Z]*"
    Const TextPatternCE As String = "*[!0-9A-Z]*"
        
'   See if anything entered/copied into column
    Set RngB = Intersect(Target, Range("B4:B28"))
    Set RngCE = Intersect(Target, Range("C4:C28"))
    
'----------------------------------------------------------------------------------------------------------------

'   Exit if nothing put in watched column
    If RngB Is Nothing Then Exit Sub
    
'   Loop through updated values in watched range
    For Each CellB In RngB
    
'   See if length exceeds 3
        If Len(CellB) > 3 Then
            Application.EnableEvents = False
            CellB.Value = Left(CellB, 3)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellB.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
        End If
        
'   See if characters are in UPPERCASE
        If CellB.Text Like TextPatternB Then
            Application.EnableEvents = False
            CellB.Value = ""
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellB.Address(0, 0) & " should be in UPPERCASE only", vbOKOnly, "WARNING!"
            
        End If
    Next
'----------------------------------------------------------------------------------------------------------------

'   Exit if nothing put in watched column
    If RngCE Is Nothing Then Exit Sub
    
'   Loop through updated values in watched range
    For Each CellCE In RngCE
    
'   See if length exceeds 34
        If Len(CellCE) > 34 Then
            Application.EnableEvents = False
            CellCE.Value = Left(CellCE, 34)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " limited to 34 characters", vbOKOnly, "WARNING!"
        End If
        
'   See if characters are in UPPERCASE
        If CellCE.Text Like TextPatternCE Then
            Application.EnableEvents = False
            CellCE.Value = ""
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " should be in UPPERCASE & Numbers only", vbOKOnly, "WARNING!"
            
        End If
    Next
 
End Sub
 
Upvote 0
If you want to check more than one range (which you are now doing), then you need to change this line:
VBA Code: 
    If RngB Is Nothing Then Exit Sub
as if it does not find anything in that range, it exits the sub and will never check the second range.

So you want to change it so it insteads checks to see if there is anything in that range, and if there is, loop through (so embed the loop within an IF...THEN block).

You can leave the second loop as-is, since there is nothing else after it. If you wanted to check, let's say three ranges, you would need to do the same thing to the second range.

So try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim RngB As Range
    Dim CellB As Range

    Dim RngCE As Range
    Dim CellCE As Range
    
    Const TextPatternB As String = "*[!A-Z]*"
    Const TextPatternCE As String = "*[!0-9A-Z]*"
        
'   See if anything entered/copied into column
    Set RngB = Intersect(Target, Range("B4:B28"))
    Set RngCE = Intersect(Target, Range("C4:C28"))
    
'----------------------------------------------------------------------------------------------------------------

'   Check to see if any cells in RngB to loop through
    If Not (RngB Is Nothing) Then
    
'       Loop through updated values in watched range
        For Each CellB In RngB
    
'       See if length exceeds 3
            If Len(CellB) > 3 Then
                Application.EnableEvents = False
                CellB.Value = Left(CellB, 3)
                Application.EnableEvents = True
                MsgBox "Entry in cell " & CellB.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
            End If
        
'           See if characters are in UPPERCASE
            If CellB.Text Like TextPatternB Then
                Application.EnableEvents = False
                CellB.Value = ""
                Application.EnableEvents = True
                MsgBox "Entry in cell " & CellB.Address(0, 0) & " should be in UPPERCASE only", vbOKOnly, "WARNING!"
            End If
            
        Next

    End If
'----------------------------------------------------------------------------------------------------------------

'   Exit if nothing put in watched column
    If RngCE Is Nothing Then Exit Sub
    
'   Loop through updated values in watched range
    For Each CellCE In RngCE
    
'   See if length exceeds 34
        If Len(CellCE) > 34 Then
            Application.EnableEvents = False
            CellCE.Value = Left(CellCE, 34)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " limited to 34 characters", vbOKOnly, "WARNING!"
        End If
        
'   See if characters are in UPPERCASE
        If CellCE.Text Like TextPatternCE Then
            Application.EnableEvents = False
            CellCE.Value = ""
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " should be in UPPERCASE & Numbers only", vbOKOnly, "WARNING!"
            
        End If
    Next
 
End Sub
 
Upvote 0
Joe4 said:
If you want to check more than one range (which you are now doing), then you need to change this line:
VBA Code: 
    If RngB Is Nothing Then Exit Sub
as if it does not find anything in that range, it exits the sub and will never check the second range.

So you want to change it so it insteads checks to see if there is anything in that range, and if there is, loop through (so embed the loop within an IF...THEN block).

You can leave the second loop as-is, since there is nothing else after it. If you wanted to check, let's say three ranges, you would need to do the same thing to the second range.

So try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim RngB As Range
    Dim CellB As Range

    Dim RngCE As Range
    Dim CellCE As Range
   
    Const TextPatternB As String = "*[!A-Z]*"
    Const TextPatternCE As String = "*[!0-9A-Z]*"
       
'   See if anything entered/copied into column
    Set RngB = Intersect(Target, Range("B4:B28"))
    Set RngCE = Intersect(Target, Range("C4:C28"))
   
'----------------------------------------------------------------------------------------------------------------

'   Check to see if any cells in RngB to loop through
    If Not (RngB Is Nothing) Then
   
'       Loop through updated values in watched range
        For Each CellB In RngB
   
'       See if length exceeds 3
            If Len(CellB) > 3 Then
                Application.EnableEvents = False
                CellB.Value = Left(CellB, 3)
                Application.EnableEvents = True
                MsgBox "Entry in cell " & CellB.Address(0, 0) & " limited to 3 characters", vbOKOnly, "WARNING!"
            End If
       
'           See if characters are in UPPERCASE
            If CellB.Text Like TextPatternB Then
                Application.EnableEvents = False
                CellB.Value = ""
                Application.EnableEvents = True
                MsgBox "Entry in cell " & CellB.Address(0, 0) & " should be in UPPERCASE only", vbOKOnly, "WARNING!"
            End If
           
        Next

    End If
'----------------------------------------------------------------------------------------------------------------

'   Exit if nothing put in watched column
    If RngCE Is Nothing Then Exit Sub
   
'   Loop through updated values in watched range
    For Each CellCE In RngCE
   
'   See if length exceeds 34
        If Len(CellCE) > 34 Then
            Application.EnableEvents = False
            CellCE.Value = Left(CellCE, 34)
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " limited to 34 characters", vbOKOnly, "WARNING!"
        End If
       
'   See if characters are in UPPERCASE
        If CellCE.Text Like TextPatternCE Then
            Application.EnableEvents = False
            CellCE.Value = ""
            Application.EnableEvents = True
            MsgBox "Entry in cell " & CellCE.Address(0, 0) & " should be in UPPERCASE & Numbers only", vbOKOnly, "WARNING!"
           
        End If
    Next
 
End Sub
Click to expand...
Sorry Joe for late reply,

This is working fine now, Thank you so much for your help 😍
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Data Validation and copy - paste
Replies
1
Views
226
ExcelToDAX
E
K
  • Question
Help with VB Code to copy workbook
Replies
0
Views
154
kizzie37
K
C
  • Question
Loop validation, copy output from same range and paste in different ranges
Replies
1
Views
390
RoryA
RoryA
D
  • Question
Prevent paste from wepage overriding custom validation cell
Replies
0
Views
207
dpoag1
D
J
  • Question
Excel VBA Help
2
Replies
15
Views
393
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,214,943
Messages
6,122,376
Members
449,080
Latest member
Armadillos

Share this page

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