P
pat12
Hello world
I am still a newbie with VBA and it took me quite a long time to build
(with your help ofcourse) code presented below
------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("E2:E1001")) = False Or
HasValidation(Range("F2:F1001")) = False Or
HasValidation(Range("U2:U1001")) = False _
Or HasValidation(Range("AJ2:AJ1001")) = False Or
HasValidation(Range("AN2:AN1001")) = False Or Range("check") > 0 Then
Application.Undo
MsgBox "Operation canceled. It would destroy validation data",
vbCritical '
Else
Exit Sub
End If
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
-----------------------------------
It worked very well (filed check is sum of column in which 0 means data
in range are ok, 1 are not ok) and I was very satisfied for a moment
but then I put protection on sheet and the code just stopped working. I
have no errors. It just let me destroy validation data format without
msg box. When I unprotect sheet it is working propely again.
I am very confused
Any help appreciated
PAT
I am still a newbie with VBA and it took me quite a long time to build
(with your help ofcourse) code presented below
------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("E2:E1001")) = False Or
HasValidation(Range("F2:F1001")) = False Or
HasValidation(Range("U2:U1001")) = False _
Or HasValidation(Range("AJ2:AJ1001")) = False Or
HasValidation(Range("AN2:AN1001")) = False Or Range("check") > 0 Then
Application.Undo
MsgBox "Operation canceled. It would destroy validation data",
vbCritical '
Else
Exit Sub
End If
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
-----------------------------------
It worked very well (filed check is sum of column in which 0 means data
in range are ok, 1 are not ok) and I was very satisfied for a moment
but then I put protection on sheet and the code just stopped working. I
have no errors. It just let me destroy validation data format without
msg box. When I unprotect sheet it is working propely again.
I am very confused
Any help appreciated
PAT