Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
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 "NOW" 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 "now" function to only run if a change is made to any cell in a range?? I would like the "now" 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 Applegate |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
How about a nice worksheet_change event placed in your worksheet module by
right click sheet tabview codeinsert this. change to suitSAVE Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Cells, Range("A1:A10")) Is Nothing Then Range("h1") = Now End If End Sub -- Don Guillett SalesAid Software "George Applegate" wrote in message ... 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 "NOW" 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 "now" function to only run if a change is made to any cell in a range?? I would like the "now" 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 Applegate |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
Hi George:
Enter this into worksheet code: Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2:A10"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("A1").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 Student "George Applegate" wrote: 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 "NOW" 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 "now" function to only run if a change is made to any cell in a range?? I would like the "now" 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 Applegate |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
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 "view code" It brings up a box that says "Book1- this workbook(code) and then has scroll boxes that say "general" and "declarations". When I copy/paste the code you supplied the "private sub..." shows in red, the "range..." shows in black, and the "end sub" shows in blue. On the left side there is another window which says "project vba project" and then it has "vba project book 1" and then has "microsoft excel objects", with subs sheet 1, sheet 2, and sheet 3 and then "this workbook". "this workbook" is highlighted. I enter the code you gave, and close the "x" 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 "private sub..." 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 "Ardus Petus" wrote: Assuming 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("A1") = Format(Now, "dd/mm/yyyy hh:mm:ss") End Sub Get back to Excel Save your workbook Et voilà! George Applegate |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
That's because of line wrapping
Try this: Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Range("A1") = Format(Now, "dd/mm/yyyy hh:mm:ss") End Sub "George Applegate" a écrit dans le message de news: ... 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 "view code" It brings up a box that says "Book1- this workbook(code) and then has scroll boxes that say "general" and "declarations". When I copy/paste the code you supplied the "private sub..." shows in red, the "range..." shows in black, and the "end sub" shows in blue. On the left side there is another window which says "project vba project" and then it has "vba project book 1" and then has "microsoft excel objects", with subs sheet 1, sheet 2, and sheet 3 and then "this workbook". "this workbook" is highlighted. I enter the code you gave, and close the "x" 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 "private sub..." 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 "Ardus Petus" wrote: Assuming 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("A1") = Format(Now, "dd/mm/yyyy hh:mm:ss") End Sub Get back to Excel Save your workbook Et voilà! George Applegate |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOW function question
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 wrote: Hi George: Enter this into worksheet code: Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2:A10"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("A1").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 George Applegate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Product Function Question | Excel Discussion (Misc queries) | |||
DB (depreciation) function Help question | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
INDIRECT function question | Excel Worksheet Functions | |||
Function question | Excel Worksheet Functions |