| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cancel on Worksheet_Change
It worked great, thanks!
"Rick Rothstein (MVP - VB)" wrote: While events are shown declared as subroutines, they are really something slightly different. You cannot modify the argument list of an event... the argument list is fixed for each event. You can do the following to simulate the Cancel functionality. Delete your existing Worksheet_Change event and replace it with the following... Dim OriginalValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("XInput")) Is Nothing Then ReConfigure = MsgBox("You have selected to change X Input" & _ Chr(10) & "Pricing needs to be re - calculated" & _ Chr(10) & "Do you want to proceed?", vbYesNo) If ReConfigure = vbNo Then Application.EnableEvents = False Target.Value = OriginalValue Application.EnableEvents = True End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) OriginalValue = Target.Value End Sub Make sure the Dim OriginalValue statement is located at the top of your sheet's code window (in the General-Declarations section). Rick "Cavy" wrote in message ... Hi there, I am trying to include a Cancel option in a Worksheet_Change event, but I am having this error message when including the Cancel in the routine: €śCompile Error. Procedure declaration does not match description of even or procedure having the same name€ť My code is as follows: Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean) If Intersect(Target, Range("XInput")) Is Nothing Then Else ReConfigure = MsgBox("You have selected to change X Input" & Chr(10) & "Pricing needs to be re - calculated" & Chr(10) & "Do you want to proceed?", vbYesNo) If ReConfigure = vbNo Then Cancel = True End If End sub Could you please advice? Thanks |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Workbook_BeforeClose(Cancel As Boolean) - Cancel won't work | Setting up and Configuration of Excel | |||
| Input box to cancel sub when Cancel is clicked. | Excel Programming | |||
| Disabling 'Cancel' option when saving work (Yes/No/Cancel) | Excel Programming | |||
| Cancel Macro is user selects 'cancel' at save menu | Excel Programming | |||
Threaded Mode
