Worksheet_Change Event behaving oddly

[tiredofit]

Can someone please try this and see if you get the same problem?


In a new workbook, put the number 1 into cell A1.

Then add a Data Validation rule, stating it must be a whole number greater than 0.

Now in the VB Editor, put this is Sheet1:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) ' PUT BREAK POINT HERE.

    Application.EnableEvents = False
    
    Application.Undo
        
    Application.EnableEvents = True
    
End Sub

Put a break point in the first line of the code.

Now change the value in the Excel Sheet1 cell A1 from 1 to say 2. The Data Validation message pops up stating it's an invalid value, do you want to Retry or Cancel? Click Cancel.

The code will hit the break point. Step into it through to the end. The value in Sheet1 should return to 1, as expected.


However, if you attempt to put a letter, say a, in cell A1, the code again hits the break point but when you step into it, it goes to End Sub and when you press F8, it starts at the first line again!

Why is that?

Thanks

 

[StephenCrump]

However, if you attempt to put a letter, say a, in cell A1, the code again hits the break point but when you step into it, it goes to End Sub and when you press F8, it starts at the first line again!

.. and if you click Retry, and enter a valid number, the Sub will run three times. Worksheet_Change is triggered when you try making the change, again when Data Validation rejects it, and then again when you enter the valid number. You can temporarily suppress the latter two using .EnableEvents=False, but eventually you'll want .EnableEvents =True, and they will run then.

You can write some quick code to monitor and suppress these multiple calls. But I'm not sure what you intend by using Undo?

 

[tiredofit]

.. and if you click Retry, and enter a valid number, the Sub will run three times. Worksheet_Change is triggered when you try making the change, again when Data Validation rejects it, and then again when you enter the valid number. You can temporarily suppress the latter two using .EnableEvents=False, but eventually you'll want .EnableEvents =True, and they will run then.

You can write some quick code to monitor and suppress these multiple calls. But I'm not sure what you intend by using Undo?

The scenario is that I have written my own code to enable users to selectively change values on a worksheet.


If a cell is NOT permitted, I use .Undo to reverse the changes.


I can "sandwhich" that with Application.EnableEvents = False just prior, then turn it back on afterwards.


The problem is that if the cell the user is trying to change contains Data Validation (and they attempt to enter a non-valid value), the DV function kicks in, informing the user it is not permitted, would they like to Retry or Cancel.


That's the problem because it seems the DV function also triggers the Worksheet_Change event.

 

[StephenCrump]

If you test any particular cell using either DV or your own code, and not both (?) then it will be easy to stop your Worksheet_Change running if DV is triggered.

For example, you could

- Have Sub Worksheet_Change test if Target intersects the range your code is going to test, and proceed only in this event

or

- Exit Sub Worksheet_Change if Target has DV.

 

[tiredofit]

If you test any particular cell using either DV or your own code, and not both (?) then it will be easy to stop your Worksheet_Change running if DV is triggered.

For example, you could

- Have Sub Worksheet_Change test if Target intersects the range your code is going to test, and proceed only in this event

or

- Exit Sub Worksheet_Change if Target has DV.

Thanks for the suggestion.

I tried this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
        If ActiveCell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            MsgBox "Active Cell does not have validation"
        Else
            MsgBox "Active cell has Validation"
        End If
    On Error GoTo 0
    
End Sub

but if you put in a letter into cell A1, the code still loops twice.

 

[StephenCrump]

Perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim v As Variant
    
    On Error Resume Next
    v = Target.Validation.Type
    On Error GoTo 0
    
    If IsEmpty(v) Then
        If Target.Value <> "MyValue" Then
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
                MsgBox "Code validation failed MyValue test. Change undone!"
            End With
        End If
    End If


End Sub

I have used .Validation.Type but you could also use a .SpecialCells approach.

If you change a DV cell, the code will still trigger two or three times, but the IsEmpty() test will stop execution getting to the .Undo part.

You'll also need to allow for the possibility of Target containing more than one cell, e.g. perhaps looping through each cell and undoing the change if any cell fails your validation tests?

 

[tiredofit]

Perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim v As Variant
    
    On Error Resume Next
    v = Target.Validation.Type
    On Error GoTo 0
    
    If IsEmpty(v) Then
        If Target.Value <> "MyValue" Then
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
                MsgBox "Code validation failed MyValue test. Change undone!"
            End With
        End If
    End If


End Sub

I have used .Validation.Type but you could also use a .SpecialCells approach.

If you change a DV cell, the code will still trigger two or three times, but the IsEmpty() test will stop execution getting to the .Undo part.

You'll also need to allow for the possibility of Target containing more than one cell, e.g. perhaps looping through each cell and undoing the change if any cell fails your validation tests?

Thanks for the workaround. Will test it in due course.

 

[StephenCrump]

Thanks for the PM and the workbook, but let's keep it to the Forum please.

In any event, I'm not clear ...

You can test it by adding a range in the Cust sheet, then try changing the contents of that range in Sheet1.

Are you trying to monitor changes in a range with formulae pointing to another worksheet? Changes in value of a formula won't trigger Worksheet_Change, and you'll need to use Worksheet_Calculate instead.

The problem is I want the user to be informed by a message box whenever they try to change a locked cell.

It works but the message box pops up multiple times if the cell contains a DV and the user attempts to enter an invalid value.

Your solution posted on the forum bypasses the message but I would like it to appear, though only once.

Are you describing your "Cell is locked" message? Why would you want this to show if the user is entering an invalid value in a DV (unlocked) cell?

 

[tiredofit]

Thanks for the PM and the workbook, but let's keep it to the Forum please.

In any event, I'm not clear ...



Are you trying to monitor changes in a range with formulae pointing to another worksheet? Changes in value of a formula won't trigger Worksheet_Change, and you'll need to use Worksheet_Calculate instead.



Are you describing your "Cell is locked" message? Why would you want this to show if the user is entering an invalid value in a DV (unlocked) cell?

Thanks for taking the time to look my workbook.

Originally I wasn't sure I could strip out most of the contents, hence PMed you.

The purpose of my own lock / unlock function is that a "super" user can restrict what a "normal" user can change on Sheet1. The beauty of it is no passwords are required. Instead, the Cust sheet is hidden from anyone but the "super" user.

Re your second point, I want the message box to pop up (though only once) whenever a user tries to change the contents of a locked cell, regardless of whether it contains DV or not.