Hello I would like to have a script that returns current date and time
in a cell in the active sheet when the user write in one cell.
This can not be very hard to do but I do not know how.

Please help.


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065

Paste this code into the code of the worksheet you want to do your work on.
If you have existing code, then Option Explicit should be at the top of the
code.

Option Explicit forces you as a programmer to define every variable that you
will use.

'Code starts here
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'This checks to see if the change was made in
column A
Application.EnableEvents = False 'Prevents running this again with
the following change
Sheet1.Cells(Target.Row, 2).Value = Date & " " & Time 'This assigns
column B to the current Time (possibly date also)
Application.EnableEvents = True 'Reenables events
End If

End Sub

"a94andwi" wrote:


Hello I would like to have a script that returns current date and time
in a cell in the active sheet when the user write in one cell.
This can not be very hard to do but I do not know how.

Please help.


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065

GB Wrote:

'Code starts here
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'This checks to see if the change was made
in
column A
Application.EnableEvents = False 'Prevents running this again with
the following change
Sheet1.Cells(Target.Row, 2).Value = Date & " " & Time 'This assigns
column B to the current Time (possibly date also)
Application.EnableEvents = True 'Reenables events
End If

End Sub

QUOTE]


Hello.

This works great. I'm interested in developing this function so that it
erases the date and time column when column 1, 2, 3 are erased/cleared.
This must be very easy but when you do not know how to code in VBA then
it isn't.

Is there a simple way to handle this?

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column < 4 Then
With Target
If .Value = "" Then
.Offset(0,1).Value = ""
Else
.Offset(0, 1).Value = Date & " " & Time
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

GB Wrote:

'Code starts here
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'This checks to see if the change was made
in
column A
Application.EnableEvents = False 'Prevents running this again with
the following change
Sheet1.Cells(Target.Row, 2).Value = Date & " " & Time 'This assigns
column B to the current Time (possibly date also)
Application.EnableEvents = True 'Reenables events
End If

End Sub

QUOTE]


Hello.

This works great. I'm interested in developing this function so that it
erases the date and time column when column 1, 2, 3 are erased/cleared.
This must be very easy but when you do not know how to code in VBA then
it isn't.

Is there a simple way to handle this?

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065

Thank you very much.
Is there maybe a way to put this function in a if-statement that check
if it is only row 3 to 65550 that are affected

--
a94andw
-----------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50106

It's easier to ask for all you want at the start, rather than bit by bit


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A3:C65550"

On Error GoTo ws_exit:
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Offset(0,1).Value = ""
Else
.Offset(0, 1).Value = Date & " " & Time
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

Thank you very much.
Is there maybe a way to put this function in a if-statement that checks
if it is only row 3 to 65550 that are affected?


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065

This adds it to the next column along

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 1).Value = Now
.Offset(0, 1).NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"a94andwi" wrote in
message ...

Hello I would like to have a script that returns current date and time
in a cell in the active sheet when the user write in one cell.
This can not be very hard to do but I do not know how.

Please help.


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile:
http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=501065