Hi all,
I record a macro that unprotect a sheet and attached it to a form button.
When i click the button the unprotect sheet (coming from the application)
form appears. the problem is that if i click the 'Cancel' button the rest of
my code is still executed.(this i want prevent). how can i trap the cancel
event and prevent it from executing the rest of the code?
Thanks in advance

Hi Lp_12,

how can i trap the cancel event

You could use the userform's QueryClose event. For example:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
' Cancel button pressed, do something. e.g.:
MsgBox "You pressed the red X!"
Else
'do something else, e.g.:
MsgBox "You closed the form without using the red X"
End If
End Sub


---
Regards,
Norman



"Lp12" wrote in message
...
Hi all,
I record a macro that unprotect a sheet and attached it to a form button.
When i click the button the unprotect sheet (coming from the application)
form appears. the problem is that if i click the 'Cancel' button the rest
of
my code is still executed.(this i want prevent). how can i trap the cancel
event and prevent it from executing the rest of the code?
Thanks in advance

Hi Norman,
Thanks for the reply.
I wanted to know how does my code knows that the evet started?
Do i hae to copy it to my custom sub or to call it to another funkction?
Thanks again

"Norman Jones" wrote:

Hi Lp_12,

how can i trap the cancel event

You could use the userform's QueryClose event. For example:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
' Cancel button pressed, do something. e.g.:
MsgBox "You pressed the red X!"
Else
'do something else, e.g.:
MsgBox "You closed the form without using the red X"
End If
End Sub


---
Regards,
Norman



"Lp12" wrote in message
...
Hi all,
I record a macro that unprotect a sheet and attached it to a form button.
When i click the button the unprotect sheet (coming from the application)
form appears. the problem is that if i click the 'Cancel' button the rest
of
my code is still executed.(this i want prevent). how can i trap the cancel
event and prevent it from executing the rest of the code?
Thanks in advance

Hi Lp_12,

I wanted to know how does my code knows that the evet started?

The UserForm_QueryClose event is called whenever the form is closed.

Try adding the suggested demo code to the userform module. Then try closing
your form, firstly by using the X close icon and, secondly, by your
programmatic means - via a commandbutton, perhaps.


---
Regards,
Norman



"Lp12" wrote in message
...
Hi Norman,
Thanks for the reply.
I wanted to know how does my code knows that the evet started?
Do i hae to copy it to my custom sub or to call it to another funkction?
Thanks again

"Norman Jones" wrote:

Hi Lp_12,

how can i trap the cancel event

You could use the userform's QueryClose event. For example:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
' Cancel button pressed, do something. e.g.:
MsgBox "You pressed the red X!"
Else
'do something else, e.g.:
MsgBox "You closed the form without using the red X"
End If
End Sub


---
Regards,
Norman



"Lp12" wrote in message
...
Hi all,
I record a macro that unprotect a sheet and attached it to a form
button.
When i click the button the unprotect sheet (coming from the
application)
form appears. the problem is that if i click the 'Cancel' button the
rest
of
my code is still executed.(this i want prevent). how can i trap the
cancel
event and prevent it from executing the rest of the code?
Thanks in advance

Hi Norman,
I want to clarify that i didn't create any custom userform rather i'm using
the:
ActiveSheet.UnProtect which opens up the excel own "Unprotect" form asking
for the password.
Do i make any sense?
Thanks for the patience

"Norman Jones" wrote:

Hi Lp_12,

I wanted to know how does my code knows that the evet started?

The UserForm_QueryClose event is called whenever the form is closed.

Try adding the suggested demo code to the userform module. Then try closing
your form, firstly by using the X close icon and, secondly, by your
programmatic means - via a commandbutton, perhaps.


---
Regards,
Norman



"Lp12" wrote in message
...
Hi Norman,
Thanks for the reply.
I wanted to know how does my code knows that the evet started?
Do i hae to copy it to my custom sub or to call it to another funkction?
Thanks again

"Norman Jones" wrote:

Hi Lp_12,

how can i trap the cancel event

You could use the userform's QueryClose event. For example:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
' Cancel button pressed, do something. e.g.:
MsgBox "You pressed the red X!"
Else
'do something else, e.g.:
MsgBox "You closed the form without using the red X"
End If
End Sub


---
Regards,
Norman



"Lp12" wrote in message
...
Hi all,
I record a macro that unprotect a sheet and attached it to a form
button.
When i click the button the unprotect sheet (coming from the
application)
form appears. the problem is that if i click the 'Cancel' button the
rest
of
my code is still executed.(this i want prevent). how can i trap the
cancel
event and prevent it from executing the rest of the code?
Thanks in advance

Hi Lp_12,

I want to clarify that i didn't create any custom userform

Sorry - I misunderstood!

Please post the code that you have assigned to the button

---
Regards,
Norman



"Lp12" wrote in message
...
Hi Norman,
I want to clarify that i didn't create any custom userform rather i'm
using
the:
ActiveSheet.UnProtect which opens up the excel own "Unprotect" form asking
for the password.
Do i make any sense?
Thanks for the patience

"Norman Jones" wrote:

Hi Lp_12,

I wanted to know how does my code knows that the evet started?

The UserForm_QueryClose event is called whenever the form is closed.

Try adding the suggested demo code to the userform module. Then try
closing
your form, firstly by using the X close icon and, secondly, by your
programmatic means - via a commandbutton, perhaps.


---
Regards,
Norman



"Lp12" wrote in message
...
Hi Norman,
Thanks for the reply.
I wanted to know how does my code knows that the evet started?
Do i hae to copy it to my custom sub or to call it to another
funkction?
Thanks again

"Norman Jones" wrote:

Hi Lp_12,

how can i trap the cancel event

You could use the userform's QueryClose event. For example:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = vbFormControlMenu Then
' Cancel button pressed, do something. e.g.:
MsgBox "You pressed the red X!"
Else
'do something else, e.g.:
MsgBox "You closed the form without using the red X"
End If
End Sub


---
Regards,
Norman



"Lp12" wrote in message
...
Hi all,
I record a macro that unprotect a sheet and attached it to a form
button.
When i click the button the unprotect sheet (coming from the
application)
form appears. the problem is that if i click the 'Cancel' button the
rest
of
my code is still executed.(this i want prevent). how can i trap the
cancel
event and prevent it from executing the rest of the code?
Thanks in advance