We have a worksheet that we want users to be able to press a button on the
worksheet & which in the next cell near the button, it would stamp the
current time and/or date from the system. Then the cell with the date and/or
time would be unable to be changed once the button is pressed (locked)

1. First select all the cells and unlock them (Format
Cells Protection tab).
2. Go to View Toolbars and select "Control Toolbox".
3. Click on the command button icon once and draw a
button on your worksheet.
4. Right-click the button and go to "View Code". Paste in
the following:

Private Sub CommandButton1_Click()
With ActiveSheet
.Unprotect Password:="sesami"
With [J1]
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
.Locked = True
End With
.Protect Password:="sesami"
End With
End Sub

5. Press ALT+Q.
6. Right-click on the button again, go to Properties, and
change the Caption property. Exit Properties.
6. Click on the icon "Exit Design Mode" on the Control
Toolbox toolbar.
7. Close the toolbar.

HTH
Jason
Atlanta, GA


-----Original Message-----
We have a worksheet that we want users to be able to
press a button on the
worksheet & which in the next cell near the button, it
would stamp the
current time and/or date from the system. Then the cell
with the date and/or
time would be unable to be changed once the button is
pressed (locked)
.

thanks, it seems to do the trick.

"Jason Morin" wrote:

1. First select all the cells and unlock them (Format
Cells Protection tab).
2. Go to View Toolbars and select "Control Toolbox".
3. Click on the command button icon once and draw a
button on your worksheet.
4. Right-click the button and go to "View Code". Paste in
the following:

Private Sub CommandButton1_Click()
With ActiveSheet
.Unprotect Password:="sesami"
With [J1]
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
.Locked = True
End With
.Protect Password:="sesami"
End With
End Sub

5. Press ALT+Q.
6. Right-click on the button again, go to Properties, and
change the Caption property. Exit Properties.
6. Click on the icon "Exit Design Mode" on the Control
Toolbox toolbar.
7. Close the toolbar.

HTH
Jason
Atlanta, GA


-----Original Message-----
We have a worksheet that we want users to be able to
press a button on the
worksheet & which in the next cell near the button, it
would stamp the
current time and/or date from the system. Then the cell
with the date and/or
time would be unable to be changed once the button is
pressed (locked)
.

Train them to use CTRL + ; to enter a static date.


Gord Dibben Excel MVP

On Wed, 9 Mar 2005 13:53:02 -0800, abe wrote:

We have a worksheet that we want users to be able to press a button on the
worksheet & which in the next cell near the button, it would stamp the
current time and/or date from the system. Then the cell with the date and/or
time would be unable to be changed once the button is pressed (locked)

abe Wrote:
We have a worksheet that we want users to be able to press a button on
the
worksheet & which in the next cell near the button, it would stamp the
current time and/or date from the system. Then the cell with the date
and/or
time would be unable to be changed once the button is pressed (locked)

You can shopw the current date and time with a macro assigned to a
button to do what you intend.

Firstly select the cell you intend using and set the date format you
want to use. Then as you start recording your macro with the cell
still selected press(CTRL+; then SPACE then CTRL+SHIFT+; ) to set the
systems current date & time, press stop on the recording toolbar. Now
select the button you intend to use from the (View,Toolbars, Forms)
menu. A list including the macro you just created will appear, assign
this macro to the button. Change the text of the button to whatever the
user will understand


--
D ][\/][ C

When I tried recording such a macro, the "Stop Recording Macro" command was
not available until after confirming/entering the contents of the cell. The
macro did not record the "Ctrl + :" or the "Ctrl + Shift + ;", but rather
just entered the date, space, and time into the macro itself. Running the
macro subsequently just entered the date and time when the macro was created.
I went in and looked at the macro's code, and it looks like this:

Sub DateTimeStamp()
'
' DateTimeStamp Macro
' Macro recorded 04/24/2007 by Steve Vincent
'

'
ActiveCell.FormulaR1C1 = "4/24/2007 13:55"
Application.Goto Reference:="DateTimeStamp"
End Sub

Any suggestions of another method?



"D ][/][ C" wrote:


abe Wrote:
We have a worksheet that we want users to be able to press a button on
the
worksheet & which in the next cell near the button, it would stamp the
current time and/or date from the system. Then the cell with the date
and/or
time would be unable to be changed once the button is pressed (locked)

You can shopw the current date and time with a macro assigned to a
button to do what you intend.

Firstly select the cell you intend using and set the date format you
want to use. Then as you start recording your macro with the cell
still selected press(CTRL+; then SPACE then CTRL+SHIFT+; ) to set the
systems current date & time, press stop on the recording toolbar. Now
select the button you intend to use from the (View,Toolbars, Forms)
menu. A list including the macro you just created will appear, assign
this macro to the button. Change the text of the button to whatever the
user will understand


--
D ][\/][ C