+ Reply to Thread
Results 1 to 3 of 3

Preventing copy in data validation field not working for multiple columns

Hybrid View

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Preventing copy in data validation field not working for multiple columns

    I am trying to prevent users from pasting into data validation field. I got the macro from http://j-walk.com/ss/excel/tips/tip98.htm which is working only for one column. When I select multiple columns which have diff data validations, its not working. Either its restricting the paste across all cells in the sheet(Not in selected range "ComValRngCom") or its allowing in all cells. Following is the code. Pls help.

    Private Sub Worksheet_Change(ByVal Target As Range)

    'Does the validation range still have validation?

    Application.EnableEvents = False

    If HasValidation(Range("ComValRngCom")) And HasValidation(Range("ComValRngCntry")) And HasValidation(Range("ComValRngFType")) Then

    Exit Sub
    Else
    Application.Undo
    MsgBox "Your last operation was canceled." & _
    "It would have deleted data validation rules.", vbCritical
    End If

    Application.EnableEvents = True


    End Sub

    Private Function HasValidation(r) As Boolean
    ' Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
    End Function

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Preventing copy in data validation field not working for multiple columns

    You will need to check the validation in all the cells in target.

    so
    for each c in target.cells
      if HasValidation(c) then 
          exit sub
      end if
    next c
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Preventing copy in data validation field not working for multiple columns

    Thanks nathansav. That helped.

+ 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. Preventing Data Validation
    By rakumar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2013, 03:10 AM
  2. Prevent copy paste over data validation in multiple columns
    By vivek_83anands in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 01:15 PM
  3. Data Validation not working with Copy and Paste
    By Quagga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2010, 02:03 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. [SOLVED] data validation-Can there be a setting somewhere that is preventing it?
    By Jan Buckley in forum Excel General
    Replies: 2
    Last Post: 05-04-2006, 02:40 PM

Tags for this Thread

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