Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
I have a spreadsheet that is used for checking items out and in. I a
trying to put two buttons on each item row, that can be used t Date/Time Stamp when the Item is checked out and in. I want to be able to have each (button) click action enter a Date/Tim Stamp in the same row in the column directly to the right of th button. I have tried a variety of methods to avail and I am sure tha there is a simple method that eludes me. I have little to n experience with VB. Essentially, the solution should be generic enough so that the sam macro can be activated by any button. My thinking is that if the macr could only take the location of the button (clicked) into account an then offset the Date/Time Stamp Data by one column this would be th solution. At the moment I am working with the following: Sub Time_Stamp() ActiveCell.Select ActiveCell.FormulaR1C1 = Now() End Sub What do I need to change to accomplish my objective? Thank you a advance for any assistance that can be provided. JP -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
You could put the time stamp button on the toolbar at the top of you
worksheet using a macro button then assign a macro something like Sub timeanddate() ActiveCell.Offset(0, 1) = Now End Sub When the button is clicked it will select the cell next to the activ one and enter the date and time. HTH Simo -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
Thanks Simon, but while using the suggested code would work it is no
that different from the code I have now (I just click in a cell whic makes it active and then activate the Date/Time Stamp macro.) I guess to clarify my situation; items are checked Out and In randoml so this solution would not in itself be any better than what I am no doing. However, if I could figure out how to make the buttons location the active cell when clicked (which is a random event) thi would work perfectly. The Offset instruction would push the answer t the right cell. I am looking to make the spreadsheet as simple to us as possible to avoid user error. This is next should give all a clearer idea of what I am trying t accomplish. Example: Item CheckedOUT CheckedIN XYZitem [Button] 08/20/04 7PM [Button] 08/20/04 8PM XYZitem [Button] 08/21/04 3AM [Button] 08/21/04 2PM Thanks again Simon and all. Any additional input is appreciated! JP -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
Thanks Simon, but while using the suggested code would work it is not
that different from the code I have now (I just click in a cell which makes it active and then activate the Date/Time Stamp macro.) I guess to clarify my situation; items are checked Out and In randomly so this solution would not in itself be any better than what I am now doing. However, if I could figure out how to make the buttons' location the active cell when clicked (which is a random event) this would work perfectly. The Offset instruction would push the answer to the right cell. I am looking to make the spreadsheet as simple to use as possible to avoid user error. This is next should give all a clearer idea of what I am trying to accomplish. Example: Item --------------- CheckedOUT ------------CheckedIN XYZitem [Button] 08/20/04 7PM [Button] 08/20/04 8PM XYZitem [Button] 08/21/04 3AM [Button] 08/21/04 2PM Thanks again Simon and all. Any additional input is appreciated! JPM --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
As simple as possible???.......ok try this!
Choose a cell you wont use, lets say A1 now type =NOW()and pres return, the current date and time will now be displayed here. Select the area's you want the date stamp to appear in eg. columnA hol down CRTL and choose another column or group of cells and so on, no right click and choose FORMAT, NUMBERSand then CUSTOM scroll down unti you see dd/mm/yyyy hh:mm and choose this......., when you have done thi choose DATA from the menu bar and then choose VALIDATION and then fro the allow selection choose LIST a pop up box will appear now click th cell we have NOW in (A1) then click the red and grey square to th right of the formula bar on the pop up box. Now when you select an cell in your selected range an arrow will appear at the side of th cell if you click this and then choose the only entry there is it wil date stamp that cell. HTH Simo -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date/Time Stamp Macro Button Problem
Thanks Simon!
Sorry it took so long to get back to you. Your solution works great; simple yet elegant solution, better than my ideas on the problem. JPM --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date-time stamp | Excel Discussion (Misc queries) | |||
Date and time stamping with a (macro) button | Excel Worksheet Functions | |||
Can excel have a button to input (stamp) the systems date and/or . | Excel Worksheet Functions | |||
Date and time stamp, control button | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) |