Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi there, I'm trying to write a macro, so that if a cell in a range has a valu placed in it - Now() is written to a corresponding cell in anothe column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvent set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I a still quite new to VBA! Thanks Ro -- systemati ----------------------------------------------------------------------- systematic's Profile: http://www.excelforum.com/member.php...fo&userid=2529 View this thread: http://www.excelforum.com/showthread.php?threadid=49610 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Rob,
Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Set rng = Me.Range("F6:F45") With Target If .Count 1 Then Exit Sub If Not Intersect(rng, .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime) End If Application.EnableEvents = True End If End With End Sub '<<============= --- Regards, Norman "systematic" wrote in message ... Hi there, I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I am still quite new to VBA! Thanks Rob -- systematic ------------------------------------------------------------------------ systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294 View this thread: http://www.excelforum.com/showthread...hreadid=496107 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Norman,
Thanks for the answer to systematic's question. Trying the code out it works well if the cell is directly entered. But if you copy down or you paste into more than one cell it does not enter the date. Is there any solution to this problem? TIA. "Norman Jones" wrote: Hi Rob, Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Set rng = Me.Range("F6:F45") With Target If .Count 1 Then Exit Sub If Not Intersect(rng, .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime) End If Application.EnableEvents = True End If End With End Sub '<<============= --- Regards, Norman "systematic" wrote in message ... Hi there, I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I am still quite new to VBA! Thanks Rob -- systematic ------------------------------------------------------------------------ systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294 View this thread: http://www.excelforum.com/showthread...hreadid=496107 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Bill,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Bill,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Rob,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Rob,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Rob,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
systematic wrote: Hi there, I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I am still quite new to VBA! Thanks Rob Hi try Private Sub Worksheet_Change(ByVal Target As Range) If Union(Target, Range("$F$6:$F$45")).Address = "$F$6:$F$45" Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, -2).Value = Now Else Target.Offset(0, -2).Value = "no call taken" End If Application.EnableEvents = True End If End Sub Note the following changes: 1) VBA has its own version of Now() - no need to bother with paste special 2) \ is not an escape character in VBA \" throws a syntax error 3) unless you have a reason to use relative addresses - just accept the default behavior of range.address and insert $ where needed. To get a feel of how things work, play around with the following macro: Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub 4) If you change *one* cell in F6:F45, then the address of the target will be that of the changed cell and not all of F6:F45. If you want to monitor a range for any change, you need to use the union operator as above 5) the offset from the target has the effect of only changing the corresponding cell in the D column, and not the entire range of cells 6) I always use the slightly verbose range.value rather than just range (which defaults to value) - but this is a matter of taste. I find explicit code easier to debug 7) I assume you didn't really want "1" for the value (as opposed to 1) - if you really wanted the string you can revert to "1" If you are new to VBA then Walkenbach's VBA books are great learning tools. Hope that helps -John Coleman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Rob,
Is what you are trying to do really going to overcome the problem of each Now being updated or overwritten?. When you use VBA to place Now in a cell it doesn't put the Now function into that cell it just puts in the current Now value, ie a static date and time value. Each time your code runs it will update the cell with the new Now value and you lose the old one. If you don't want to lose any old Now values you will need to use the Offset method to shift all the older Now values across one column or down one row. For example: when the following code is run it puts the current Now value into B1 then shifts all previous Now values down one row by way of the Offset method copying each value into the cell immediately below. When the loop reaches B1 that too is copied into B2. Public Sub SaveOldNows() Range("B1").Value = Now For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1 'Shift old time values down 1 row Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value Next iRowOffset End Sub Also, what is the effect of the "\"'s in your code. I've never seen anything like it before? <if range(\"f6:f45\") = \"1\" then Also, in the first line of your code are you intending to detect if the sheet change has occurred anywhere in the range F6:F45? If that is the case then the way I have seen it done is... If Intersect(Target,Range("F6:F45")) Is Nothing then Exit Sub End If Hope this helps you. Ken Johnson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Hi Martin,
Trying the code out it works well if the cell is directly entered. But if you copy down or you paste into more than one cell it does not enter the date. Is there any solution to this problem? The code was specifically limited to a single cell by the line: If .Count 1 Then Exit Sub Is there any solution to this problem? Try something like:: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Dim rCell As Range Set rng = Intersect(Target, Me.Range("F6:F45")) Application.EnableEvents = False For Each rCell In rng With rCell If Not Intersect(rng, .Cells) Is Nothing Then If Not IsEmpty(.Value) Then With .Offset(0, 1) If IsEmpty(.Value) Then .Value = FormatDateTime(Now, vbLongTime) End If End With End If End If End With Next rCell Application.EnableEvents = True End Sub '<<============= --- Regards, Norman "Martin Fishlock" wrote in message ... Norman, Thanks for the answer to systematic's question. Trying the code out it works well if the cell is directly entered. But if you copy down or you paste into more than one cell it does not enter the date. Is there any solution to this problem? TIA. "Norman Jones" wrote: Hi Rob, Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Set rng = Me.Range("F6:F45") With Target If .Count 1 Then Exit Sub If Not Intersect(rng, .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime) End If Application.EnableEvents = True End If End With End Sub '<<============= --- Regards, Norman "systematic" wrote in message ... Hi there, I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I am still quite new to VBA! Thanks Rob -- systematic ------------------------------------------------------------------------ systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294 View this thread: http://www.excelforum.com/showthread...hreadid=496107 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
Sure, you can loop through the entire range of interest each time and update
cells where a date has not been entered as required ( a value is in a cell within the range.). You could prevent copying into the range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("F6:F45") If Not Intersect(Target, rng) Is Nothing Then Application.CutCopyMode = False End If End Sub You could make a formula dependent on the range and utilize the calculate event to clean up the range You could use the selection change to affect the ability to drag I don't know if any combination would be foolproof, but you could certainly come close. Obviously the more defensive/usually unnecessary checking you do, the more it will affect performance. -- Regards, Tom Ogilvy "Martin Fishlock" wrote in message ... Norman, Thanks for the answer to systematic's question. Trying the code out it works well if the cell is directly entered. But if you copy down or you paste into more than one cell it does not enter the date. Is there any solution to this problem? TIA. "Norman Jones" wrote: Hi Rob, Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Set rng = Me.Range("F6:F45") With Target If .Count 1 Then Exit Sub If Not Intersect(rng, .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then .Offset(0, 1).Value = FormatDateTime(Now, vbLongTime) End If Application.EnableEvents = True End If End With End Sub '<<============= --- Regards, Norman "systematic" wrote in message ... Hi there, I'm trying to write a macro, so that if a cell in a range has a value placed in it - Now() is written to a corresponding cell in another column, then the value copied and pasted (so that it does not update). I have this - private sub worksheet_change(byval target as range) if target.address(false, false) = \"f6:f45\" then application.enableevents = false if range(\"f6:f45\") = \"1\" then range(\"d6:d45\").value = now() else range(\"d6:d45\") = \"no call taken\" end if application.enableevents = true end if range(\"d6:d45\").select selection.copy range(\"d6:d45\").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false end sub For some reason it does not want to work for me. I have EnableEvents set to True on workbook open and am running Office XP. Would appreciate any advice to steer me in the right direction as I am still quite new to VBA! Thanks Rob -- systematic ------------------------------------------------------------------------ systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294 View this thread: http://www.excelforum.com/showthread...hreadid=496107 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert Now() when cell updates
First of all, what is exactly not working?
Here a few tips: I would break the procedure up in different parts and see if the individual functionality works. Not sure if it makes sense to compare entire ranges, maybe it's better to just compare individual cells? Why do you compare it to 1? Then, you also copy the whole range again, not sure if this is what you wanna do. I think you only wanna copy the new cell, no? Cheers Remy Blaettler http://www.collaboral.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if/then insert date but don't want automatic updates | Excel Discussion (Misc queries) | |||
'IF' Macro to insert cell contents to alternate cell if cell not e | Excel Worksheet Functions | |||
Macro to Insert Comment to a cell | Excel Programming | |||
Macro updates | Excel Programming | |||
Macro updates and link goes #REF! ??? | Links and Linking in Excel |