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

I am affraid that there is no such event. You can use the selection change
event however to get the value of the cell when they first enter and before
they modify. Store this as a Public Variable. When they make the change let
them know the error of their ways and put back the value you stored when they
first entered the cell. It is kind of round about, but it works in a pinch.

HTH

"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

....I figured that was my only alternative, but I had to ask :) Somehow Excel
must have access to "other" events that aren't visible to VBA users.

-Fabricio

"Jim Thomlinson" wrote:

I am affraid that there is no such event. You can use the selection change
event however to get the value of the cell when they first enter and before
they modify. Store this as a Public Variable. When they make the change let
them know the error of their ways and put back the value you stored when they
first entered the cell. It is kind of round about, but it works in a pinch.

HTH

"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

You can access other events using event handler code. You need to declare an
event handler class and a whole pile of other stuff that is a whole lot more
work that it is worth in a lot of cases. Not to mention I am not sure if
there is an event generated to be caught here.

"Fabricio" wrote:

...I figured that was my only alternative, but I had to ask :) Somehow Excel
must have access to "other" events that aren't visible to VBA users.

-Fabricio

"Jim Thomlinson" wrote:

I am affraid that there is no such event. You can use the selection change
event however to get the value of the cell when they first enter and before
they modify. Store this as a Public Variable. When they make the change let
them know the error of their ways and put back the value you stored when they
first entered the cell. It is kind of round about, but it works in a pinch.

HTH

"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

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

Thanks Dave this was helpful... I used to code without the message box

"Dave Peterson" wrote:

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