Where is VBA's BeforeSheetChange event?

[Bland]

In Excel 2002 event:

Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Target.Text is the cell's value *after* changing.

How do I get the cell's value *before* the change?

Excel 2002 Help lists no such event. Object Sh seems to be null. And a
Range object doesn't have a "PreviousValue" property, does it?

Two, if that functionality exists, how do I cancel the change and
prevent the cell from updating?

My purpose is: I need to prohibit users from copying cells to targets
that already have data. In that case I want them to rekey the new value
(s) manually. I'm getting burned by people overwriting whole ranges of
data and screwing themselves up. (If they want to copy data to a blank
target, that's OK. Solving the problem by, say, requiring them to lock/
unlock cells would be an impossible kludge.)

It seems that the event above should rather be called
Workbook_AfterSheetChange, shouldn't it? So where's its Before
partner?

Thanks much.

***

 

[Dave Peterson]

There's not beforesheetchange event.

But you could do:

with application
.enableevents = false 'don't make it call itself!
.undo 'put things back
'do what you need to do to check
'if things are ok, then undo the undo
.undo
.enableevents = true
end with

Gord Dibben has posted routines that
save the values in the changed range
does an Application.undo
and assigns the values to the changed range.

Here's a version that may get you started:
http://groups.google.com/group/micr...+group:microsoft.public.excel.*+author:dibben

or
http://snipurl.com/tojry

 

[Bland]

Dave:

I was just about to post another comment or two when I saw your
lightning-fast reply.

Thanks VERY much. Will do.

***

 

[Bland]

Dave (or anyone):

Uh-oh. In this process, I need to know how to determine if a Copy/
Paste (or Move/Paste) is pending.

I will let the user change any cell if he re-keys its value. But I
want to intercept him if he's about to Paste (or, in terms of the very
ingenious code you submitted, "undo the cell if it had been" a Paste).

Is there a way to distinguish between a Paste and direct input?

Thanks much.

***

 

[Dave Peterson]

Not that I know.

But Gord's code actually undid the paste (or any type of change), and just
assigned the values.

That sounds like what you want to do.

 

[Bland]

Dave:

OK thanks, once again.

***