Problems trapping Cancels

[BillySilly]

Hi
I have a cell L5 validated for a Date and the following code comes into play if the cell contents is changed.

On the sheet code page

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$5" Then Call Change
End Sub

On a regular module

Code:

Sub Change()
MsgBox "Hello" ‘code will be something useful
End Sub


If I select the cell and start to change it by clicking in the formula bar I get the OK tick and Cancel X on the formula bar.
If without changing it I click the OK tick or if I click on the Cancel X it leaves the cell unchanged and Change is not called….fine
If I enter a valid date and click the OK tick Change is called…. fine
If I enter an invalid date and click the OK tick it returns the validation Msg Box with buttons for Retry and Cancel but puts the invalid date in L5. When I do enter a valid date Change is called…..fine
If I click Cancel in the Msg box or Cancel (X) in the formula bar it reverts to the original L5 contents which triggers Change….infine
How do I modify the code so that neither of the Cancels will trigger Change?

 

[cytop]

It would have been handy if you included the actual code for Sub Change()

Bit it sounds like the code is altering the cell contents...? Clearing it, for example?

If so, use

Application.EnableEvents = False

You must re-enable EventHandling before exiting the procedure. Ideally, you should include an error handler as the Application state is being changed. The Error trap should ensure any Application properties are reset before the procedure exits.

 

[BillySilly]

Hi
The macro Change has nothing in it at the moment as it just shows me that the changed cell event has been recognised.
I am still a relative newbie
I have also just noticed that if an invalid date has been entered I get the MsgBox and if I click on Retry, Change is called twice. If I retry twice it is called three times.

 

[cytop]

Sorry - I can't replicate that behavior in XL 2007, even with Data Validation for dates set in the cell.

 

[BillySilly]

Hi
Try 29/02/13

 

[BillySilly]

No problem have found another way to do what I want