Hi,
Well, I always get the same problem, even with this error handling...
After fighting with the spreadsheet, I found out what was causing the
problem: apparently you need to unprotect the sheet twice, once before every
offset! It works like this, but I still can't understand why:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vrange As Range
Dim vvrange As Range
Dim cell As Object
Me.Unprotect Password:="my_password"
Set vrange = Range("ID_Conf")
Set vvrange = Range("Approval_Granted_For")
For Each cell In Target
If Union(cell, vrange).Address = vrange.Address Then
Target.Offset(0, 1).Value = Application.UserName
Me.Unprotect Password:="my_password"
Target.Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY")
ElseIf Union(cell, vvrange).Address = vvrange.Address Then
Target.Offset(0, 1).Value = Month(Now - 33 + 30 * Target.Cells.Value) & "/"
& "01/" & Year(Now - 33 + 30 * Target.Cells.Value)
End If
Next cell
On Error GoTo 0
Me.Protect Password:="my_password"
End Sub

Does somebody know the reason for this? I am quite curious now!
Many thanks,
best regards,
Valeria

"Dave Peterson" wrote:

I'm not quite sure what goes into those ranges, but maybe this will get you
closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vrange As Range
Dim vvrange As Range
Dim cell As Object
Set vrange = Range("ID_Conf")
Set vvrange = Range("Approval_Granted_For")
Me.Unprotect Password:="my_password"

Application.EnableEvents = False
On Error Resume Next
For Each cell In Target
If Union(cell, vrange).Address = vrange.Address Then
Target.Offset(0, 1).Value = Application.UserName
Target.Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY")
ElseIf Union(cell, vvrange).Address = vvrange.Address Then
Target.Offset(0, 1).Value = Month(Now - 33 + 30 _
* Target.Cells.Value) & "/" _
& "01/" & Year(Now - 33 + 30 * Target.Cells.Value)
End If
Next cell
On Error goto 0
Me.Protect Password:="my_password"
End Sub



Valeria wrote:

Hi Frank,
I have tried, but it still does not work! When I look at my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vrange As Range
Dim vvrange As Range
Dim cell As Object
Set vrange = Range("ID_Conf")
Set vvrange = Range("Approval_Granted_For")
Me.Unprotect Password:="my_password"
For Each cell In Target
If Union(cell, vrange).Address = vrange.Address Then
Target.Offset(0, 1).Value = Application.UserName
Target.Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY")
ElseIf Union(cell, vvrange).Address = vvrange.Address Then
Target.Offset(0, 1).Value = Month(Now - 33 + 30 * Target.Cells.Value) & "/"
& "01/" & Year(Now - 33 + 30 * Target.Cells.Value)
End If
Next cell
On Error GoTo errhandler
errhandler:
Application.EnableEvents = True
Me.Protect Password:="my_password"
End Sub

I realize that the problem is in the Target.Offset(0, 2).Value =
Format(Date, "DD-MMM-YYYY") line.
If I comment it out, everything works. What can't understand is that,
without the protection, this code works well also for the second offset, and
with the protection, this second offset will give me an "application-defined
or object-defined error".
I have locked the cells in both columns offset 1 and 2, their format is the
same.

What can be the source of error? I am out of ideas!

Many thanks in advance!

Best regards,
Valeria

"Frank Kabel" wrote:

Hi
in your macro use

me.unprotect password:="your_password"
'some code
me.protect password:="your_password"

--
Regards
Frank Kabel
Frankfurt, Germany

"Valeria" schrieb im Newsbeitrag
...
Dear Experts,
I have a workbook which contains some VBA "Worksheet_Change" code.
Basically, when a user makes a change in a certain cell, the cell
next to it
changes to reflect the name of this user.
This is a security check for me, to make sure that the person who has
actually inputed the cell is also the one allowed to do so.
But the cell containing his/her name must be protected, otherwise
everybody
can change it... only when I try to do so, I get an error from the
Worksheet_Change code, as it's not allowed anymore to input the name
in the
protected cell.
Is there a way I can allow VBA to write in these otherwise protected
cells
for everybody else?

I hope I was clear, if not please let me know and I'll rephrase my
problem!

Thanks in advance for your precious and needed help!
Best regards,

--
Valeria



--

Dave Peterson