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

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

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

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

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

One of the lines I had in my suggested code was this:
application.enableevents = false

You don't have that in your code.

Try putting a breakpoint on the first "me.unprotect" line.
(Just click on that line and hit F9--it'll have a red "fill" color.)

Now back to your worksheet and type something into the ID_conf range and hit
enter.

When the code gets to that breakpoint it'll stop and wait for you. Hit F8 to
continue executing your code.

Watch the path that it takes--it goes through your code multiple times. When
you change something, that causes the event to fire a second time (even if it's
the code that's doing the changing).

Add one of those "application.enableevents = false" to the top of your code
(right before the "For each cell" line) and see what happens the next time you
change something.

ps. Didn't my suggestion work? It did for me.



Valeria wrote:

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


--

Dave Peterson