Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
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. *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
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/micro...+author:dibben or http://snipurl.com/tojry Bland wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
Dave:
I was just about to post another comment or two when I saw your lightning-fast reply. Thanks VERY much. Will do. *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
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. *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where is VBA's BeforeSheetChange event?
Dave:
OK thanks, once again. *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PUTTING VBA'S TOGETHER | Excel Worksheet Functions | |||
working with 2 or vba's on the same sheet | Excel Programming | |||
Conflicting VBA's | Excel Programming | |||
Bug in Excel's (not VBA's) MOD function | Excel Discussion (Misc queries) | |||
VBA's Future | Excel Programming |