Re : Excel Event Worksheet_Change Shuns SpecialCells(xlCellTypeAllValidation)
 

Re : Excel Event Worksheet_Change Shuns
SpecialCells(xlCellTypeAllValidation)

1. Open an Excel worksheet and enter the following code of
Worksheet_Change Event in the worksheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "SheetChange Event : Activated"

V = Application.Intersect(Selection,
Me.Cells.SpecialCells(xlCellTypeAllValidation))

MsgBox "SheetChange Event : follows
SpecialCells(xlCellTypeAllValidation)"

End Sub

2. Cells A1 and B1 contain Data Validation.

3. Upon deleting cell B2, the worksheet enters into the state of
Worksheet_Change Event.

4. The message box displays "SheetChange Event : Activated" ; it
shows that the Event is fired.

5. After the message box is clicked away, another message box is
expected to display "SheetChange Event : follows
SpecialCells(xlCellTypeAllValidation)"

6. However, multiple attempts to show the second message box appear in
vain.

7. The code execution is apparently shying at the expression of
SpecialCells(xlCellTypeAllValidation) and therefore it veers round the
second message box as well subsequently.

8. There's not any run-time error message.

9. Please share your comments. Regards.