Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
timestamp functions
I have a spreadsheet that crunches averages, deviations, and such for running
times of scenes, acts, and entire performances of theatrical productions based on start and end times for each unit of the production. However, it is very difficult for a stage manager to constantly enter the time while calling cues--I have found the control shift semicolon shortcut, but would prefer a function that updates itself on the return key, allowing a single keystroke to both update a field and advance to the next one. Is there a way to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
timestamp functions
you could put a pic (small clock ect) in the cell you want the time to be
logged and link it to the macro below,, this will insert the time in place of the pic (means only have to click to enter the time) Sub enter_time() Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm" ActiveSheet.Shapes(Application.Caller).Delete ActiveSheet.Protect End Sub "alexfthe" wrote: I have a spreadsheet that crunches averages, deviations, and such for running times of scenes, acts, and entire performances of theatrical productions based on start and end times for each unit of the production. However, it is very difficult for a stage manager to constantly enter the time while calling cues--I have found the control shift semicolon shortcut, but would prefer a function that updates itself on the return key, allowing a single keystroke to both update a field and advance to the next one. Is there a way to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
timestamp functions
sorry you will need to delete the
ActiveSheet.Protect line i forgot to remove it from code i use (unless you want to protect sheet then leave it in and enter ActiveSheet.unProtect just above the range line "Rich Mcc" wrote: you could put a pic (small clock ect) in the cell you want the time to be logged and link it to the macro below,, this will insert the time in place of the pic (means only have to click to enter the time) Sub enter_time() Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm" ActiveSheet.Shapes(Application.Caller).Delete End Sub "alexfthe" wrote: I have a spreadsheet that crunches averages, deviations, and such for running times of scenes, acts, and entire performances of theatrical productions based on start and end times for each unit of the production. However, it is very difficult for a stage manager to constantly enter the time while calling cues--I have found the control shift semicolon shortcut, but would prefer a function that updates itself on the return key, allowing a single keystroke to both update a field and advance to the next one. Is there a way to do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
timestamp functions
Great, thanks! This will help a lot.
Is there any way to attach it to a cell itself rather than the image? (to allow the macro to run on striking enter) Or Is there a way to allow the application caller to regenerate in a remote location and prepare to perform the action on the next cell down? (would create a large alarm clock icon in the corner and have it insert a timestamp in a cell, then clicking the icon again inserts the timestamp one cell below the previous timestamp and so on...) Thanks Now Using: Sub Timestamp() ' ' Timestamp Macro ' Macro recorded 3/9/2006 by Alex ' ' Keyboard Shortcut: Ctrl+t ' Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm:ss" ActiveSheet.Shapes(Application.Caller).Delete End Sub "Rich Mcc" wrote: sorry you will need to delete the ActiveSheet.Protect line i forgot to remove it from code i use (unless you want to protect sheet then leave it in and enter ActiveSheet.unProtect just above the range line "Rich Mcc" wrote: you could put a pic (small clock ect) in the cell you want the time to be logged and link it to the macro below,, this will insert the time in place of the pic (means only have to click to enter the time) Sub enter_time() Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm" ActiveSheet.Shapes(Application.Caller).Delete End Sub "alexfthe" wrote: I have a spreadsheet that crunches averages, deviations, and such for running times of scenes, acts, and entire performances of theatrical productions based on start and end times for each unit of the production. However, it is very difficult for a stage manager to constantly enter the time while calling cues--I have found the control shift semicolon shortcut, but would prefer a function that updates itself on the return key, allowing a single keystroke to both update a field and advance to the next one. Is there a way to do this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
timestamp functions
sorry for the delay ,, im not an expert at v,b so had to work it out
try this , hope it helps change "b" in the 2 nextfree statments to the coloum number where you want to enter the time Sub enter_time() Dim nextfree As String timerow = 1 nextfree = "b" & timerow Do While Range(nextfree) < 0 timerow = timerow + 1 nextfree = "b" & timerow Loop Range(nextfree).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm" End Sub "alexfthe" wrote: Great, thanks! This will help a lot. Is there any way to attach it to a cell itself rather than the image? (to allow the macro to run on striking enter) Or Is there a way to allow the application caller to regenerate in a remote location and prepare to perform the action on the next cell down? (would create a large alarm clock icon in the corner and have it insert a timestamp in a cell, then clicking the icon again inserts the timestamp one cell below the previous timestamp and so on...) Thanks Now Using: Sub Timestamp() ' ' Timestamp Macro ' Macro recorded 3/9/2006 by Alex ' ' Keyboard Shortcut: Ctrl+t ' Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm:ss" ActiveSheet.Shapes(Application.Caller).Delete End Sub "Rich Mcc" wrote: sorry you will need to delete the ActiveSheet.Protect line i forgot to remove it from code i use (unless you want to protect sheet then leave it in and enter ActiveSheet.unProtect just above the range line "Rich Mcc" wrote: you could put a pic (small clock ect) in the cell you want the time to be logged and link it to the macro below,, this will insert the time in place of the pic (means only have to click to enter the time) Sub enter_time() Dim sAddress As String sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address(0, 0) Range(sAddress).Select ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues Selection.NumberFormat = "hh:mm" ActiveSheet.Shapes(Application.Caller).Delete End Sub "alexfthe" wrote: I have a spreadsheet that crunches averages, deviations, and such for running times of scenes, acts, and entire performances of theatrical productions based on start and end times for each unit of the production. However, it is very difficult for a stage manager to constantly enter the time while calling cues--I have found the control shift semicolon shortcut, but would prefer a function that updates itself on the return key, allowing a single keystroke to both update a field and advance to the next one. Is there a way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Changing the range of several averaging functions | Excel Discussion (Misc queries) | |||
Visible rows and functions that work | Excel Worksheet Functions |