| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Stamp with protection
Hi,
I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Since your code doesn't exit before the end of it all, the simplest thing to
do would be to put Worksheet.Unprotect just before the With Target statement, then put Worksheet.Protect right after the End With for the block, just before End Sub. If you have the sheet protected with a password, it would be (substitute appropriate password) Worksheet.Unprotect password:="mySheetPassword" and Worksheet.Protect password:="mySheetPassword" "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Sam.
Fot this to work you must in turn select columns H, Y & AG and right click, format cells - protection and un-check 'Locked'. I've also combined 2 of your sections of code into 1 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Hi,
sorry but that didn't work, it comes up with a run time error. "JLatham" wrote: Since your code doesn't exit before the end of it all, the simplest thing to do would be to put Worksheet.Unprotect just before the With Target statement, then put Worksheet.Protect right after the End With for the block, just before End Sub. If you have the sheet protected with a password, it would be (substitute appropriate password) Worksheet.Unprotect password:="mySheetPassword" and Worksheet.Protect password:="mySheetPassword" "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Hi Mike,
That worked great. Ther only other thing I am looking for is that each time it adds a date/time stamp that the whole spreadsheet saves. Are you able to help? Thanks "Mike H" wrote: Sam. Fot this to work you must in turn select columns H, Y & AG and right click, format cells - protection and un-check 'Locked'. I've also combined 2 of your sections of code into 1 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Sam.
Like this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi Mike, That worked great. Ther only other thing I am looking for is that each time it adds a date/time stamp that the whole spreadsheet saves. Are you able to help? Thanks "Mike H" wrote: Sam. Fot this to work you must in turn select columns H, Y & AG and right click, format cells - protection and un-check 'Locked'. I've also combined 2 of your sections of code into 1 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp with protection
Mike, that's exactly what I wanted.
Thank you...again. "Mike H" wrote: Sam. Like this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" ThisWorkbook.Save Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi Mike, That worked great. Ther only other thing I am looking for is that each time it adds a date/time stamp that the whole spreadsheet saves. Are you able to help? Thanks "Mike H" wrote: Sam. Fot this to work you must in turn select columns H, Y & AG and right click, format cells - protection and un-check 'Locked'. I've also combined 2 of your sections of code into 1 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents .Offset(0, -4).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then ActiveSheet.Unprotect Password:="MyPass" Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Hi, I have the following text running as a macro in my spreadsheet, however I now want to do two things, I have protected the cells where the date stamps appear, therefore I need to know what and where I put the bit in my macro to unprotect and re-prrotect the cells. Also everytime it carries out the date stamping I would also like it to save the entire spreadsheet. Any help would be great. Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -5).ClearContents Else With .Offset(0, -5) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("H:H"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -4).ClearContents Else With .Offset(0, -4) .NumberFormat = "hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy hh:mm" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If Application.EnableEvents = True End If End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Stamp | Excel Discussion (Misc queries) | |||
| Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
| Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
| Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions | |||
| date stamp | Excel Discussion (Misc queries) | |||
![Sam H[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
