software

Our company has six locations and we have a master spreadsheet in
place which has a workbook for each location. During the day,
employees from that location will go in and make changes to inventory
measurements on 5-10 products that are tracked.

Then, also during the day, someone in the central office can pull up
the spreadsheet and check the inventories and decide if more product
needs to be ordered.

Problem: would like to know when the last time measurements have been
entered for a location without the user having to enter the date/time.
The quot;NOWquot; function would work great, but it updates the workbook cell
whenever you open it, or whenever the workbook is calculated. Is
there a way to CONDITION the quot;nowquot; function to only run if a change is
made to any cell in a range??

I would like the quot;nowquot; function cell to only change if a change is
made to the worksheet, not just by someone going in and viewing it.

thanks,
ga
George ApplegateAssuming you want the timestamp in cell A1,

Right click on the Excel icon on top left corner of your worbook's window
Select Code
Paste the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range(quot;A1quot;) = Format(Now, quot;dd/mm/yyyy hh:mm:ssquot;)
End Sub

Get back to Excel
Save your workbook

Et voilà!

--
AP

quot;George Applegatequot; gt; a écrit dans le message de news:
...
gt; Our company has six locations and we have a master spreadsheet in
gt; place which has a workbook for each location. During the day,
gt; employees from that location will go in and make changes to inventory
gt; measurements on 5-10 products that are tracked.
gt;
gt; Then, also during the day, someone in the central office can pull up
gt; the spreadsheet and check the inventories and decide if more product
gt; needs to be ordered.
gt;
gt; Problem: would like to know when the last time measurements have been
gt; entered for a location without the user having to enter the date/time.
gt; The quot;NOWquot; function would work great, but it updates the workbook cell
gt; whenever you open it, or whenever the workbook is calculated. Is
gt; there a way to CONDITION the quot;nowquot; function to only run if a change is
gt; made to any cell in a range??
gt;
gt; I would like the quot;nowquot; function cell to only change if a change is
gt; made to the worksheet, not just by someone going in and viewing it.
gt;
gt; thanks,
gt; ga
gt; George Applegate
gt;
How about a nice worksheet_change event placed in your worksheet module by
right click sheet tabgt;view codegt;insert this. change to suitgt;SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Cells, Range(quot;A1:A10quot;)) Is Nothing Then
Range(quot;h1quot;) = Now
End If
End Sub

--
Don Guillett
SalesAid Software

quot;George Applegatequot; gt; wrote in message
news
gt; Our company has six locations and we have a master spreadsheet in
gt; place which has a workbook for each location. During the day,
gt; employees from that location will go in and make changes to inventory
gt; measurements on 5-10 products that are tracked.
gt;
gt; Then, also during the day, someone in the central office can pull up
gt; the spreadsheet and check the inventories and decide if more product
gt; needs to be ordered.
gt;
gt; Problem: would like to know when the last time measurements have been
gt; entered for a location without the user having to enter the date/time.
gt; The quot;NOWquot; function would work great, but it updates the workbook cell
gt; whenever you open it, or whenever the workbook is calculated. Is
gt; there a way to CONDITION the quot;nowquot; function to only run if a change is
gt; made to any cell in a range??
gt;
gt; I would like the quot;nowquot; function cell to only change if a change is
gt; made to the worksheet, not just by someone going in and viewing it.
gt;
gt; thanks,
gt; ga
gt; George Applegate
gt;
Hi George:

Enter this into worksheet code:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range(quot;A2:A10quot;), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range(quot;A1quot;).Value = Now()
Application.EnableEvents = True
End Sub

The routine will activate if cells in the range A2 thru A10 are changed via
editting.
When the change occurs, the date/time is deposited in cell A1.

REMEMBER: worksheet code
--
Gary's Studentquot;George Applegatequot; wrote:

gt; Our company has six locations and we have a master spreadsheet in
gt; place which has a workbook for each location. During the day,
gt; employees from that location will go in and make changes to inventory
gt; measurements on 5-10 products that are tracked.
gt;
gt; Then, also during the day, someone in the central office can pull up
gt; the spreadsheet and check the inventories and decide if more product
gt; needs to be ordered.
gt;
gt; Problem: would like to know when the last time measurements have been
gt; entered for a location without the user having to enter the date/time.
gt; The quot;NOWquot; function would work great, but it updates the workbook cell
gt; whenever you open it, or whenever the workbook is calculated. Is
gt; there a way to CONDITION the quot;nowquot; function to only run if a change is
gt; made to any cell in a range??
gt;
gt; I would like the quot;nowquot; function cell to only change if a change is
gt; made to the worksheet, not just by someone going in and viewing it.
gt;
gt; thanks,
gt; ga
gt; George Applegate
gt;
gt;

Ardus,

I tried this and it didn't work so I must be doing something wrong,
but what???

I right clicked the excel icon, and then selected quot;view codequot;
It brings up a box that says quot;Book1- this workbook(code)
and then has scroll boxes that say quot;generalquot; and quot;declarationsquot;.

When I copy/paste the code you supplied the quot;private sub...quot; shows in
red, the quot;range...quot; shows in black, and the quot;end subquot; shows in blue.

On the left side there is another window which says quot;project vba
projectquot; and then it has quot;vba project book 1quot; and then has quot;microsoft
excel objectsquot;, with subs sheet 1, sheet 2, and sheet 3 and then quot;this
workbookquot;. quot;this workbookquot; is highlighted.

I enter the code you gave, and close the quot;xquot; in the right hand corner.

I save the worksheet but I don't see it doing anything in cell a1. I
close it/reopen it, and still nothing is displayed. Yet if I
right-click on the excel icon again, it still shows the code snippet I
pasted.

Is something wrong since the quot;private sub...quot; code is shown in red???

This is exactly what I want if you can help me figure out what I am
doing wrong in the entry, or point me to another place where I can see
a live example or something.

THANKS SO MUCH IN ADVANCE FOR YOUR HELP!!
ga

quot;Ardus Petusquot; gt; wrote:

gt;Assuming you want the timestamp in cell A1,
gt;
gt;Right click on the Excel icon on top left corner of your worbook's window
gt;Select Code
gt;Paste the following:
gt;
gt;Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;Boolean)
gt; Range(quot;A1quot;) = Format(Now, quot;dd/mm/yyyy hh:mm:ssquot;)
gt;End Sub
gt;
gt;Get back to Excel
gt;Save your workbook
gt;
gt;Et voilà!

George ApplegateThat's because of line wrapping
Try this:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Range(quot;A1quot;) = Format(Now, quot;dd/mm/yyyy hh:mm:ssquot;)
End Sub

quot;George Applegatequot; gt; a écrit dans le message de news:
...
gt; Ardus,
gt;
gt; I tried this and it didn't work so I must be doing something wrong,
gt; but what???
gt;
gt; I right clicked the excel icon, and then selected quot;view codequot;
gt; It brings up a box that says quot;Book1- this workbook(code)
gt; and then has scroll boxes that say quot;generalquot; and quot;declarationsquot;.
gt;
gt; When I copy/paste the code you supplied the quot;private sub...quot; shows in
gt; red, the quot;range...quot; shows in black, and the quot;end subquot; shows in blue.
gt;
gt; On the left side there is another window which says quot;project vba
gt; projectquot; and then it has quot;vba project book 1quot; and then has quot;microsoft
gt; excel objectsquot;, with subs sheet 1, sheet 2, and sheet 3 and then quot;this
gt; workbookquot;. quot;this workbookquot; is highlighted.
gt;
gt; I enter the code you gave, and close the quot;xquot; in the right hand corner.
gt;
gt; I save the worksheet but I don't see it doing anything in cell a1. I
gt; close it/reopen it, and still nothing is displayed. Yet if I
gt; right-click on the excel icon again, it still shows the code snippet I
gt; pasted.
gt;
gt; Is something wrong since the quot;private sub...quot; code is shown in red???
gt;
gt; This is exactly what I want if you can help me figure out what I am
gt; doing wrong in the entry, or point me to another place where I can see
gt; a live example or something.
gt;
gt; THANKS SO MUCH IN ADVANCE FOR YOUR HELP!!
gt; ga
gt;
gt; quot;Ardus Petusquot; gt; wrote:
gt;
gt;gt;Assuming you want the timestamp in cell A1,
gt;gt;
gt;gt;Right click on the Excel icon on top left corner of your worbook's window
gt;gt;Select Code
gt;gt;Paste the following:
gt;gt;
gt;gt;Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;gt;Boolean)
gt;gt; Range(quot;A1quot;) = Format(Now, quot;dd/mm/yyyy hh:mm:ssquot;)
gt;gt;End Sub
gt;gt;
gt;gt;Get back to Excel
gt;gt;Save your workbook
gt;gt;
gt;gt;Et voilà!
gt;
gt; George Applegate
gt;
Okay,

I think I finally figured out what I was doing wrong. To make a long
story short, this works great!

THANK YOU ALL FOR YOUR ASSISTANCE!!
ga

Gary''s Student gt; wrote:

gt;Hi George:
gt;
gt;Enter this into worksheet code:
gt;
gt;Sub Worksheet_Change(ByVal Target As Excel.Range)
gt;If Intersect(Range(quot;A2:A10quot;), Target) Is Nothing Then Exit Sub
gt;Application.EnableEvents = False
gt;Range(quot;A1quot;).Value = Now()
gt;Application.EnableEvents = True
gt;End Sub
gt;
gt;The routine will activate if cells in the range A2 thru A10 are changed via
gt;editting.
gt;When the change occurs, the date/time is deposited in cell A1.
gt;
gt;REMEMBER: worksheet code

George Applegate