You may be able to do what you want with worksheet_change.

If you keep track of what the change was, do an application.undo to revert to
before the change, then do what you want, and reapply the changes.

Something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormula As String
Dim resp As Long

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

myFormula = Target.Formula
With Application
.EnableEvents = False
.Undo
resp = MsgBox(Prompt:="did you really mean to change " _
& Target.Address(0, 0) & "?", Buttons:=vbYesNo)
If resp = vbNo Then
'do nothing
Else
Target.Formula = myFormula
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Fabricio wrote:

I'm looking for a workbook (or worksheet) event that gets triggered before
Excel changes the worksheet. The SheetChange event gets triggered after the
changes have been made to the worksheet--too late for me :)

I'm trying to mimic the response a user gets when she tries to modify a
single cell in an array formula. Excel replies with a message box "You
cannot change part of an array." and does not allow her to make changes.

Thanks in advance,
-Fabricio


--

Dave Peterson