Date/Time Stamp Macro Button Problem

[jp_m8]

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

 

[Simon Lloyd]

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

 

[jp_m8]

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

 

[jp_m8]

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

 

[Simon Lloyd]

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

 

[jp_m8]

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