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

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" <(E-Mail Removed)> wrote in message
news:12C89A44-AFA2-4080-B153-(E-Mail Removed)...
> 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
>

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

"Cavy" wrote:

> 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
>

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


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:12C89A44-AFA2-4080-B153-(E-Mail Removed)...
>> 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
>>
>

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" <(E-Mail Removed)> wrote in message
> news:12C89A44-AFA2-4080-B153-(E-Mail Removed)...
> > 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
> >
>
>