events

[ranswert]

I am trying to write a code in a change event in a worksheet. In the code I
need to unprotect the sheet and then protect the sheet when it is done. I
called a procedure in a module.

Sub protectsheet()
ActiveSheet.Protect
End Sub
Sub unprotectsheet()
ActiveSheet.Unprotect
End Sub

When the change event procedure ran it stopped working. I tried removing
all the code and did a simple msgbox("") and that didn't work either. What
do I need to do to unprotect and protect the sheet to run the procedure?

Also, how do I get the change event to execute again?

 

[JLGWhiz]

I'm not sure that I would run the protect and unprotect from a worksheet
module using the change event. It seems to work better from the VBA module.
If the Unprotect code is triggered by the change event and you have protected
the sheet, the code cannot run because the change will not occur.

 

[Gord Dibben]

Why not invoke unprotect and protect within the event code and dump the two
subs?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value <> "" _
And Target.Offset(0, 1).Value = "" Then
With Target.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben MS Excel MVP

 

[ranswert]

I will give that a try.
Thanks

Why not invoke unprotect and protect within the event code and dump the two
subs?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value <> "" _
And Target.Offset(0, 1).Value = "" Then
With Target.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben MS Excel MVP