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

Here you go Norman,


Sub UnProtect()

On Error Resume Next
Application.ScreenUpdating = False

' Setting the users last cursor position
Currrange = ActiveCell.Address
' Checking for the Verify column
Range("A3").Select
i = 1
Do
If ActiveCell.Value < "Verify By" Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If
Loop Until ActiveCell.Value = "Verify By"
Vercol = ActiveCell.Column
mycol = ActiveCell.Column
Range(Currrange).Select
If ActiveCell.Column < mycol Then
MsgBox "Please set the cursor in the row you want to verify"
Exit Sub
End If
ActiveSheet.UnProtect
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Do
ActiveCell.Offset(0, -1).Locked = True
ActiveCell.Offset(0, -1).Select
mycol = mycol - 1
Loop Until mycol = 1
ActiveSheet.Protect Password:="3455" _
, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub

thanks

"Norman Jones" wrote:

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

Hi Lp_12,

Try something like:

'===========================
Public Sub UnProtect()
Dim Currrange As Range
Dim i As Long
Dim Vercol As Long
Dim mycol As Long

On Error Resume Next
Application.ScreenUpdating = False

' Setting the users last cursor position
Currrange = ActiveCell.Address
' Checking for the Verify column
Range("A3").Select
i = 1
Do
If ActiveCell.Value < "Verify By" Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If
Loop Until ActiveCell.Value = "Verify By"
Vercol = ActiveCell.Column
mycol = ActiveCell.Column
Range(Currrange).Select
If ActiveCell.Column < mycol Then
MsgBox "Please set the cursor in the row you want to verify"
Exit Sub
End If
ActiveSheet.UnProtect
If Not IsProtected(ActiveSheet) Then
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Do
ActiveCell.Offset(0, -1).Locked = True
ActiveCell.Offset(0, -1).Select
mycol = mycol - 1
Loop Until mycol = 1
ActiveSheet.Protect Password:="3455", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowInsertingHyperlinks:=True
Else
MsgBox ActiveSheet.Name & " has not been unprotected!"
End If
End Sub

'--------------------------------------------

Function IsProtected(Optional WS As Worksheet) As Boolean
If WS Is Nothing Then Set WS = ActiveSheet
If WS.ProtectScenarios Or _
WS.ProtectDrawingObjects Or _
WS.ProtectContents Then
IsProtected = True
End If

End Function
'<<===========================


Apart from using a call to the additional IsProtected function, I have
explicitly dimmed your variables, which is good practice.

I have not otherwise attempted to understand your procedures logic.


---
Regards,
Norman



"Lp12" wrote in message
...
Here you go Norman,


Sub UnProtect()

On Error Resume Next
Application.ScreenUpdating = False

' Setting the users last cursor position
Currrange = ActiveCell.Address
' Checking for the Verify column
Range("A3").Select
i = 1
Do
If ActiveCell.Value < "Verify By" Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If
Loop Until ActiveCell.Value = "Verify By"
Vercol = ActiveCell.Column
mycol = ActiveCell.Column
Range(Currrange).Select
If ActiveCell.Column < mycol Then
MsgBox "Please set the cursor in the row you want to verify"
Exit Sub
End If
ActiveSheet.UnProtect
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Do
ActiveCell.Offset(0, -1).Locked = True
ActiveCell.Offset(0, -1).Select
mycol = mycol - 1
Loop Until mycol = 1
ActiveSheet.Protect Password:="3455" _
, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub

thanks

"Norman Jones" wrote:

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

Norman,
Thanks a lot for all the help.
Its working gr8...
Peace

"Norman Jones" wrote:

Hi Lp_12,

Try something like:

'===========================
Public Sub UnProtect()
Dim Currrange As Range
Dim i As Long
Dim Vercol As Long
Dim mycol As Long

On Error Resume Next
Application.ScreenUpdating = False

' Setting the users last cursor position
Currrange = ActiveCell.Address
' Checking for the Verify column
Range("A3").Select
i = 1
Do
If ActiveCell.Value < "Verify By" Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If
Loop Until ActiveCell.Value = "Verify By"
Vercol = ActiveCell.Column
mycol = ActiveCell.Column
Range(Currrange).Select
If ActiveCell.Column < mycol Then
MsgBox "Please set the cursor in the row you want to verify"
Exit Sub
End If
ActiveSheet.UnProtect
If Not IsProtected(ActiveSheet) Then
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Do
ActiveCell.Offset(0, -1).Locked = True
ActiveCell.Offset(0, -1).Select
mycol = mycol - 1
Loop Until mycol = 1
ActiveSheet.Protect Password:="3455", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowInsertingHyperlinks:=True
Else
MsgBox ActiveSheet.Name & " has not been unprotected!"
End If
End Sub

'--------------------------------------------

Function IsProtected(Optional WS As Worksheet) As Boolean
If WS Is Nothing Then Set WS = ActiveSheet
If WS.ProtectScenarios Or _
WS.ProtectDrawingObjects Or _
WS.ProtectContents Then
IsProtected = True
End If

End Function
'<<===========================


Apart from using a call to the additional IsProtected function, I have
explicitly dimmed your variables, which is good practice.

I have not otherwise attempted to understand your procedures logic.


---
Regards,
Norman



"Lp12" wrote in message
...
Here you go Norman,


Sub UnProtect()

On Error Resume Next
Application.ScreenUpdating = False

' Setting the users last cursor position
Currrange = ActiveCell.Address
' Checking for the Verify column
Range("A3").Select
i = 1
Do
If ActiveCell.Value < "Verify By" Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If
Loop Until ActiveCell.Value = "Verify By"
Vercol = ActiveCell.Column
mycol = ActiveCell.Column
Range(Currrange).Select
If ActiveCell.Column < mycol Then
MsgBox "Please set the cursor in the row you want to verify"
Exit Sub
End If
ActiveSheet.UnProtect
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Do
ActiveCell.Offset(0, -1).Locked = True
ActiveCell.Offset(0, -1).Select
mycol = mycol - 1
Loop Until mycol = 1
ActiveSheet.Protect Password:="3455" _
, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
End Sub

thanks

"Norman Jones" wrote:

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