Timestamp - automated based on the next cell
 

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

--
harpscardif
-----------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=50056

Timestamp - automated based on the next cell
 

Hi HarpsCardiff,

See JE McGimpsey's TimeStamp page at:

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


---
Regards,
Norman


"harpscardiff"
wrote in message
news:harpscardiff.21inym_1137060608.6605@excelforu m-nospam.com...

Hi there,

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

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

Any help is much appreciated!


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=500562

Timestamp - automated based on the next cell
 

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

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


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=500562

Timestamp - automated based on the next cell
 

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


"harpscardiff"
wrote in message
news:harpscardiff.21itqy_1137068102.5829@excelforu m-nospam.com...

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

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


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=500562

Timestamp - automated based on the next cell
 

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?


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=500562

Timestamp - automated based on the next cell
 

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


"harpscardiff"
wrote in message
...

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?


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=500562