Timestamp Macro

[michael.d.g]

I tried a timestamp macro and keep getting this error when I update a cell:

Run-time error '1004'
Application-defined or object-defined error"

The Macro I am using is:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column < 26 And Target.Count = 1 Then Cells(Target.Row, 26) = Now
End Sub

Column 26 is where the timestamp is located.
What do I need to do to fix this?

Moderator Comment: Please use Code Tags. I have added them today. Review the forum rules you agreed to when you joined.

[Marc L]

If Target.CountLarge = 1 Then If Target.Column < 26 Then Cells(Target.Row, 26) = Now …

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[michael.d.g]

Thank you for your response. I am however getting the same error.

This is what my macro looks like.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 Then If Target.Column < 26 Then Cells(Target.Row, 26) = Now
End Sub

[Marc L]

How unlucky you are as it well works on my side with your attachment as it is …

[michael.d.g]

I appreciate your feedback.

I got it to work if the worksheet was not protected. Is there a way I can use the vba if I have "Protect Sheet" enabled?
For some reason the VBA code will not work that way.

Is there something special I have to put in the VBA code so that it will work with Protect Sheet and not give me the

Run-time error '1004':
Application-defined or object-defined error

[Marc L]

You can use Worksheet.Unprotect method before to update the cell then Protect back the sheet just after …

[michael.d.g]

Is the Worksheet.Unprotect method something I put in the VBA? Is it a separate macro? What is the code I should use?

Thanks in advance.

[michael.d.g]

NEED HELP!

What I am trying to do in a workbook in which each Worksheet is password protected do the following:
Any edits to Columns A-Q, a timestamp will appear in Column Z.
The Private Sub below only seems to work when the Worksheet was not password protected.
I would like to insert the code, password protect it and then when a user edits columns A-Q, a timestamp appears in Column Z.

Below is the code I was using which did not work.
Thanks in advance.


Workbooks("Excel Forum example").Protect Password:="a"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge = 1 Then If Target.Column < 26 Then Cells(Target.Row, 26) = Now
End Sub


.