• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use Global event in macro

A

Abhijeet

Active Member
Hi
This macro work from same worksheet if any copy paste then work if i copy paste from different workbook then not work please tell me how to do this
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If HasValidation(Range("ValR11")) And HasValidation(Range("ValR22")) Then
  Exit Sub
  Else
  Application.EnableEvents = False
  Application.Undo
  MsgBox "Your last operation was cancelled." & _
  "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
 
This Code is work when same sheet any thing copy then not allow paste in Data Validation cells but if i copy from different workbook then this macro not work so please tell me how to disable this
 
Again, what specifically do you mean by "not work"? Have you tried stepping through the code?
 
Yes i tried this code this code is work when copy from same worksheet

If i copy from different workbook then macro not work
 
You're just repeating the same thing I'm afraid, not answering my question.

1. Are you saying that the code doesn't run at all, or that it doesn't do what you want?
2. Have you tried stepping through the code to see what is happening?
 
I have mention the answer Code is work when copy from same worksheet

My Problem is when i copy from different workbook then code not work

I never mention code is not work at all

My question is when i copy from different workbook then code not work
 
Can you please try in this workbook then copy paste data from different work then past is possible
 

Attachments

  • Data Validation disable.xlsm
    15.2 KB · Views: 2
One last try:

1. Are you saying that the code doesn't run at all when you copy from another workbook, or that it doesn't do what you want?
2. Have you tried stepping through the code to see what is happening?
 
Hi ,

What I find is as follows :

1. Suppose I copy a range from within the same workbook , and paste it in the range of interest , say D5:D15 ; the data validation within this range is deleted.

The code detects this , and correctly prevents the paste.

2. Suppose I copy a range from an external workbook , and paste it in the same range of interest ; the data validation within this range is not deleted.

The code now allows the paste.

The DV has not been deleted , but the range contents are now overwritten.

OP would like the paste to be prevented even in this case.

Narayan
 
OK, in that case the DV check is unnecessary. You can just check if they are trying to paste into the two ranges:
Code: 
Private bPasting              As Boolean

Private Sub Worksheet_Activate()
    If Application.CutCopyMode <> False Then
        If Not Intersect(ActiveCell, Union(Range("valR1"), Range("valR2"))) Is Nothing Then
            bPasting = True
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Do the validation ranges still have validation?

    If bPasting Then
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was cancelled." & _
               "It would have deleted data validation rules.", vbCritical
        bPasting = False
    End If
    Application.EnableEvents = True

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode <> False Then
        If Not Intersect(Target, Union(Range("valR1"), Range("valR2"))) Is Nothing Then
            bPasting = True
        Else
            bPasting = False
        End If
    End If
End Sub
 
Thanks for this

If multiple columns data copy & paste into this range then not prevent to the paste that data previous macro prevent that from same worksheet so please tell me if multiple column data if copy & paste into this range then how to do this

In attach workbook i paste multiple data highlighted in color
 

Attachments

  • 1Data Validation disable.xlsm
    17.6 KB · Views: 1
Perhaps this instead:
Code: 
Private bPasting              As Boolean

Private Sub Worksheet_Activate()
    If Application.CutCopyMode <> False Then
        bPasting = True
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Do the validation ranges still have validation?

    If bPasting Then
        If Not Intersect(Target, Union(Range("valR11"), Range("valR22"))) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was cancelled." & _
               "It would have deleted data validation rules.", vbCritical
        End If
    End If
   
    Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode <> False Then
        bPasting = True
    Else
        bPasting = False
    End If
End Sub
 
Thanks Narayan Sir
NARAYANK991 said:
Hi ,

What I find is as follows :

1. Suppose I copy a range from within the same workbook , and paste it in the range of interest , say D5:D15 ; the data validation within this range is deleted.

The code detects this , and correctly prevents the paste.

2. Suppose I copy a range from an external workbook , and paste it in the same range of interest ; the data validation within this range is not deleted.

The code now allows the paste.

The DV has not been deleted , but the range contents are now overwritten.

OP would like the paste to be prevented even in this case.

Narayan
Click to expand...
 
Please tell me i tried this
Is this correct or not for All worksheet
Code: 
Private bPasting              As Boolean
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Application.CutCopyMode <> False Then
        bPasting = True
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If bPasting Then
        If Not Intersect(Target, Union(Range("valR1"), Range("valR2"))) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was cancelled." & _
              "It would have deleted data validation rules.", vbCritical
        End If
    End If
 
    Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Application.CutCopyMode <> False Then
        bPasting = True
    Else
        bPasting = False
    End If
End Sub
 
You must log in or register to reply here.
Back
Top