Timestamp - automated based on the next cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

harpscardiff - 12 Jan 2006 10:08 GMT

Hi there,

I am trying to get the timestamp to function, so that if C3, fo
example, is blank then blank, else show timestamp.

I think the code must be entered on "ThisWorkbook"?

Any help is much appreciated

Reply to this Message

Norman Jones - 12 Jan 2006 10:23 GMT

Hi HarpsCardiff,

See JE McGimpsey's TimeStamp page at:

http://www.mcgimpsey.com/excel/timestamp.html

---
Regards,
Norman

> Hi there,
>
[quoted text clipped - 4 lines]
>
> Any help is much appreciated!

Reply to this Message

harpscardiff - 12 Jan 2006 12:11 GMT

Hi there,

Thanks for your reply, unfortunaltely i'm having trouble with the code
below. I've copied it straight from the mcgimpsey website, changed the
range, but nothings updating.

In column A3 is a formula, which is if column J blank then "" else 1.
This column will always be 1, but still not getting any automated
date?

Any ideas , Cheers,

Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("a3:a500"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--------------------

Signature

harpscardiff

Reply to this Message

Norman Jones - 12 Jan 2006 12:53 GMT

Hi HarpsCardiff,

Your code works for me.

Perhaps you have inadvertently turned off Events.

In a standard module (not a sheet module), run the following:

'=============>>
Sub Temp()
Application.EnableEvents = True
End Sub
'<<=============

Now retry the previous code.

BTW, you have pasted JE's code into the sheet nodule and not a standard
module?

---
Regards,
Norman

> Hi there,
>
[quoted text clipped - 30 lines]
>
> --------------------

Reply to this Message

harpscardiff - 24 Jan 2006 10:44 GMT

I got it working but..... As I need to proctect the sheet, I get a
runtime error; 1004, - Unable to set the NumberFormat Property of the
Range Class.

Is there any way around this, besides not locking the worksheet?

Signature

harpscardiff

Reply to this Message

Norman Jones - 24 Jan 2006 11:50 GMT

Hi HarpsCardiff,

Setting the Protect method's UserInterfaceOnly parameter to true enables vba
manipulation of the protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

'=============>>
Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", _
UserInterfaceOnly:=True
End With
End Sub
'<<=============

---
Regards,
Norman

> I got it working but..... As I need to proctect the sheet, I get a
> runtime error; 1004, - Unable to set the NumberFormat Property of the
> Range Class.
>
> Is there any way around this, besides not locking the worksheet?

Reply to this Message