Writing update date and time in a cell on inputs

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.

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

If you revise the If Target.column < 4 then
to

If Target.Column < 4 and (Target.Row >=4 and Target.Row <=65500) then

It will then ensure that it is captured within the first 3 columns and every
row within that range.

"a94andwi" wrote:

>
> 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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[QUOTE]

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

QUOTE]

I agree with the above but as you get one thing to work you see some properties that you don't like that much. That's why I bring you more questions

I have a before_save function that writes the save date in field C1. When I save it allso updates the offset cell with date. This is not what I want and that's why I asked for the changes that didn't affect row 1-3.
This don't seem to work.

Do you have any ideas.

Do you know how to make a loop that writes the save date in one cell on each worksheet?
Today I'm using hardcoded scripts which is "stupid" when I add new worksheets.

If the update date is not programmatically called from the before save
function, then if you set application.enableevents to false, or autocalculate
(or whatever function does autocalculate) before saving, then it will not
update the field(s) you are concerned about.

What I would do to "finish" the before save and maintain control of the
above actions, is to verify that the file should be saved by checking the
workbook.changed (or similar) function for a true value. If it has changed
then force the save. Set a value (don't remember which it is) in the before
save function to force Excel not to continue with the save operation. Then
revert to the original values of the enableevents/autocalculate functions.


"a94andwi" wrote:

>
> >
> >
> > It's easier to ask for all you want at the start, rather than bit by
> > bit
> >
> > QUOTE]
> >
> > I agree with the above but as you get one thing to work you see some
> > properties that you don't like that much. That's why I bring you more
> > questions
> >
> > I have a before_save function that writes the save date in field C1.
> > When I save it allso updates the offset cell with date. This is not
> > what I want and that's why I asked for the changes that didn't affect
> > row 1-3.
> > This don't seem to work.
> >
> > Do you have any ideas.
> >
> > Do you know how to make a loop that writes the save date in one cell on
> > each worksheet?
> > Today I'm using hardcoded scripts which is "stupid" when I add new
> > worksheets.
>
>
> --
> a94andwi
> ------------------------------------------------------------------------
> a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
> View this thread: http://www.excelforum.com/showthread...hreadid=501065
>
>

Hello.

This is my beforeSave function.
As you can see it is hardcoded to the names on the worksheets. This is not what I want it to be. I rather have a loop that loops through all worksheets and update all of them.

Regarding the "cancel_before_save" function you talk about I do not know how I do that. Maybo a simple if-statement that checks if any other functions are activated...

Can you help?


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Kvalitet").Range("C1") = "Senast uppdaterad " & Date
Sheets("Feedback").Range("C1") = "Senast uppdaterad " & Date
Sheets("Ekonomi").Range("C1") = "Senast uppdaterad " & Date
Sheets("Förpackningsteknik").Range("C1") = "Senast uppdaterad " & Date
Sheets("Godsavsändningen").Range("C1") = "Senast uppdaterad " & Date
Sheets("CKD").Range("C1") = "Senast uppdaterad " & Date
Sheets("Orderkontoret").Range("C1") = "Senast uppdaterad " & Date
Sheets("ÄO-Gruppen").Range("C1") = "Senast uppdaterad " & Date
Sheets("Transport").Range("C1") = "Senast uppdaterad " & Date
Sheets("Beredning").Range("C1") = "Senast uppdaterad " & Date
Sheets("Lev. besök etc.").Range("C1") = "Senast uppdaterad " & Date
Sheets("Intern materialstyrning").Range("C1") = "Senast uppdaterad " & Date
Sheets("Godsmottagning").Range("C1") = "Senast uppdaterad " & Date

End Sub

I think that if you set Cancel = true, it will cancel the "save" that is
performed when Before_save is completed. However, you will have to force a
save before completion of the Before_save subroutine.

As for your worksheet issue something like.

For each sh in Worksheets
sh.Range("C1") = "Senast uppdaterad " & Date
Next sh

Now the thing I was thinking about as far as the saving. If you are sure
that on each attempt to save, that every cell will be updated, then you can
disregard this next thing. I was trying to get to where, if no changes have
been made to the document, then you don't really need to save the document,
which is what Excel will sort of do. I.e., why rewrite the same information
if nothing has changed? It's just a waste of resources (computer and
personnel). However, perhaps in your case, you just want to go ahead and
save it anyways.

So towards the end of the before save subroutine you would have something
like.

cancel = true '(Or false depending on what help says about how this really
works.)

You may have to disable events by using application.enableevents = false to
prevent this beforesave subroutine from looping.

Workbook.save

If you have disabled events, you need to reenable them before leaving the
subroutine.

' and then the subroutine can end.

This will go through *every* sheet and perform the update of the data. If
you have any special sheets which you do not want this to occur, then you can
either use a select case or if statement or nested if(s) to see if you need
to skip the update.


"a94andwi" wrote:

>
> Hello.
>
> This is my beforeSave function.
> As you can see it is hardcoded to the names on the worksheets. This is
> not what I want it to be. I rather have a loop that loops through all
> worksheets and update all of them.
>
> Regarding the "cancel_before_save" function you talk about I do not
> know how I do that. Maybo a simple if-statement that checks if any
> other functions are activated...
>
> Can you help?
>
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Sheets("Kvalitet").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Feedback").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Ekonomi").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Förpackningsteknik").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Godsavsändningen").Range("C1") = "Senast uppdaterad " & Date
> Sheets("CKD").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Orderkontoret").Range("C1") = "Senast uppdaterad " & Date
> Sheets("ÄO-Gruppen").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Transport").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Beredning").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Lev. besök etc.").Range("C1") = "Senast uppdaterad " & Date
> Sheets("Intern materialstyrning").Range("C1") = "Senast uppdaterad " &
> Date
> Sheets("Godsmottagning").Range("C1") = "Senast uppdaterad " & Date
>
> End Sub
>
>
> --
> a94andwi
> ------------------------------------------------------------------------
> a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
> View this thread: http://www.excelforum.com/showthread...hreadid=501065
>
>