VBA data validation

[Gabriel222]

Hello,


I'm a little new to VBA, and i ran into a piece of VBA code which would prevent my data validation from being erased (either through a copy/paste or other).

I implemented the code (i believe in the right place, although i'm not sure), and upon testing the code's VBA generated message box came up about a hundred times, and basically forced me to restart excel.

Does anybody have any suggestions as to make this piece of code work?

Private Sub Worksheet_Change(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim VT As Long<o></o>
'Do all cells in the validation range<o></o>
'still have validation?<o></o>
On Error Resume Next<o></o>
VT = Range("InputRange").Validation.Type<o></o>
If Err.Number <> 0 Then<o></o>
Application.Undo<o></o>
MsgBox "Your last operation was canceled." & _<o></o>
"It would have deleted data validation rules.", vbCritical<o></o>
End If<o></o>
End Sub<o></o>

Thank you

 

[Lewiy]

Try using the line:

Application.EnableEvents = False

before the "Dim" line and:

Application.EnableEvents = True

before the "End Sub" line.

What is probably happening is that when the code changes values of a cell (i.e. during the undo command) it is triggering the code to run again. This method will stop the code recursively calling itself.

 

[Gabriel222]

thanks for the idea

although it did prevent the recursive message box,
it now forbids me to delete a cell

whereas i'm trying to prevent any change of my data validation and only my data validation...

 

[FIFARay007]

I'm also trying to make this work, and at least for me, the error message comes up all the time, regardless whether it's a valid entry or not.