| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Stamp
Hi,
I managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Sam,
Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Hi, This is the code I have.
What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Sam,
Not tested but this should combine the 2 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With End If 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, This is the code I have. What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Hi Mike,
That is excellent thank you so much, although is there any way of the second bit have abit to clear cells, if we romve the Call Resolved from the cell. thanks "Mike H" wrote: Sam, Not tested but this should combine the 2 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With End If 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, This is the code I have. What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Hi,
try this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If 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 is excellent thank you so much, although is there any way of the second bit have abit to clear cells, if we romve the Call Resolved from the cell. thanks "Mike H" wrote: Sam, Not tested but this should combine the 2 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With End If 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, This is the code I have. What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Works perfectly, thank you so much.
"Mike H" wrote: Hi, try this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If 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 is excellent thank you so much, although is there any way of the second bit have abit to clear cells, if we romve the Call Resolved from the cell. thanks "Mike H" wrote: Sam, Not tested but this should combine the 2 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With End If 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, This is the code I have. What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Stamp
Your welcome
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Sam H" wrote: Works perfectly, thank you so much. "Mike H" wrote: Hi, try this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With Else With .Offset(0, 1) .ClearContents End With End If 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 is excellent thank you so much, although is there any way of the second bit have abit to clear cells, if we romve the Call Resolved from the cell. thanks "Mike H" wrote: Sam, Not tested but this should combine the 2 Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If 'New code If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then Application.EnableEvents = False If UCase(Target.Value) = "CALL RESOLVED" Then With .Offset(0, 1) .NumberFormat = "dd/mm/yy" .Value = Now End With End If 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, This is the code I have. What I would actually like, is if anything is typed into column G then on the same row the date is entered into Cell D (which is the -3 bit below) and the Time in cell E. I would also like is that if "Call Resolved" is selected in column AE then the Date and Time is entered into column AF. 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("G:G"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -3).ClearContents Else With .Offset(0, -3) .NumberFormat = "dd/mm/yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Mike H" wrote: Sam, Events in Excel are pre-defined so you can't create another with Worksheet_Change2 it won't execute. You will have to modify the existing event code to put the timestamp somewhere else and there are ways of doing this. Post the code and details of what you want and someone will help. -- 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 managed to use the McGimpsey Macro to enter a date stamp into my spreadsheet based on something being typed into an adjacent cell. I want to then use the same thing for another part of my spreadsheet but when I copy the macro and paste it back in, in doesn't work. I've tried changing the name where it says PrivateSub Worksheet_Change to Worksheet_Change2, but it still doesn't work. Can anyone help me? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
| Date Stamp | Excel Worksheet Functions | |||
| Date Stamp Need | Excel Worksheet Functions | |||
| 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 | |||
![Sam H[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
