Excel Date and Time

[rlgaooa]

I am attempting to have today's date automatically entered into the adjacent cell when data is entered in the cell. I used this code and it works in one spread sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A3:A200"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.NumberFormat = "MM/dd/yy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

When I copy it to a new spreadsheet, it fails with Runtime error -1004

Unable to set the NumberFormat property of the Range class.

Why would it work in one spreadsheet and not the other?

 

[xlbo]

is the (2nd) sheet protected ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[rlgaooa]

Yes, the second sheet is protected, I made sure that the cells where this piece of script is running are not locked and that the users have access to the unlocked cells. When I protect the sheet the script fails. How do I enable this script to run yet still protect the sheet so the users can't change the formulas contained within it?

 

[xlbo]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("A3:A200"), .Cells) Is Nothing Then
         [b]Activesheet.unprotect "password"[/b]
          Application.EnableEvents = False
          With .Offset(0, 1)
             .NumberFormat = "MM/dd/yy hh:mm:ss"
             .Value = Now
           End With
           Application.EnableEvents = True
         [b]Activesheet.protect "password"[/b]
       End If
    End With
End Sub

where "password" is the actual password to be used

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[rlgaooa]

Thank you, that works perfectly!