Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are sometimes seconds not shown in cell? timestamp format problem | Excel Discussion (Misc queries) | |||
Can RGB fill color be automated based on 3 cell values? | Excel Discussion (Misc queries) | |||
How do I insert a Timestamp when a cell changes? | Excel Worksheet Functions | |||
How do I show a timestamp when an adjacent cell changes in value? | Excel Worksheet Functions | |||
Timestamp cell to left after update | Excel Discussion (Misc queries) |