+ Reply to Thread
Results 1 to 10 of 10

VBA Validation on Copy/Paste

Hybrid View

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    VBA Validation on Copy/Paste

    Hello, I am new to ExcelForum and relatively new to VBA.

    I am trying to validate multiple columns in a sheet that have multiple validation rules.

    - Columns A and B need to only allow alpha values
    - Column E needs to only allow the letter "X" or "x" or simply a blank
    - Columns H and J must only allow positive whole numbers between 1 and 1000.


    **I also need to ENSURE that these data validation rules stick even if someone copies and pastes data onto the sheet.

    ***If someone copies and pastes bad data, those cells should be highlighted.

    Any help would be appreciated. Thanks

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA Validation on Copy/Paste

    What do you mean by "alpha values"

    Edit: I am going to assume you mean alphanumeric

  3. #3
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    strictly alphabetic. no numbers.

  4. #4
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    strictly alphabetic. no numbers.

    can you provide some advice to my problem?

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA Validation on Copy/Paste

    This should cover all the parameters you specified

    Sub Validate_Special()
    Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
    Dim objRegExp As Object
    Dim regExp_Matches
    Dim c As Range, rng As Range
    Dim LR As Long
    
    Set objRegExp = CreateObject("vbscript.regexp")
    objRegExp.Global = True
    objRegExp.Pattern = "[^a-z^A-Z]"
    
    LR = ws.UsedRange.Rows.Count + 1
    Set rng = Union(ws.Range("A1:B" & LR), ws.Range("E1:E" & LR), ws.Range("H1:H" & LR), ws.Range("J1:J" & LR))
    
    For Each c In rng
        If Len(c) > 0 Then
            Select Case c.Column
                Case 1 To 2
                    Set regExp_Matches = objRegExp.Execute(c.Value)
                    If regExp_Matches.Count <> 0 Then
                        'string not alpha
                        c.Interior.Color = vbYellow
                    End If
                Case 5
                    If c <> "X" And c <> "x" And Len(c) <> 0 Then
                        c.Interior.Color = vbYellow
                    End If
                Case 8, 10
                    If IsNumeric(c) Then
                        If CDbl(c) - CInt(c) <> 0 Then
                            c.Interior.Color = vbYellow
                        End If
                    Else
                        c.Interior.Color = vbYellow
                    End If
            End Select
        End If
    Next c
    
    End Sub

  6. #6
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    My workbook is macro-enabled, but the code isn't stimulating anything? Am I supposed to place this within Worksheet_Change or just General?

  7. #7
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    Also, thank you so much for helping out. I really really appreciate it.

  8. #8
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    just kidding, I figured it out.

    I just entered "1.2" into a cell in Columns A and B. The code you created currently highlights ALL entered values regardless if they're incorrect or not. I just want those values that are incorrect to be highlighted.

  9. #9
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2007
    Posts
    12

    Re: VBA Validation on Copy/Paste

    Just kidding again! I figured it all out. Thank you so so much

  10. #10
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA Validation on Copy/Paste

    I am glad to hear everything worked out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy & paste with data validation
    By egarla in forum Excel General
    Replies: 1
    Last Post: 08-19-2014, 12:55 PM
  2. copy and paste validation/formulas
    By danfullwood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2013, 03:13 PM
  3. Copy and Paste a cell with Validation.
    By ANGIE@BMW in forum Excel General
    Replies: 2
    Last Post: 03-21-2013, 04:29 PM
  4. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM
  5. VBA for data validation on copy / paste
    By cherry.sharma@aol.in in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2010, 03:40 AM
  6. data validation and copy/paste.......
    By MPR in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-27-2006, 10:25 AM
  7. copy paste sheet with validation
    By Preeti in forum Excel General
    Replies: 1
    Last Post: 03-22-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1