Cancel on Worksheet_Change

[Cavy]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mike H]

Maybe this

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)
End If
If ReConfigure = vbNo Then
MsgBox "You pressed cancel"
'do nothing
Else
MsgBox "You pressed ok"
'do something
End If
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

By the way, after you set the Target value back to the OriginalValue, you
should put an Exit Sub statement AFTER the Application.EnableEvents=True
statement so any subsequent code you might have in the event procedure won't
operate on the value that was previously there.

Rick

 

[Cavy]

It worked great, thanks!

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