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

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

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

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

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


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

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