Fixed time and date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need cell "H1" to display a fixed time and date on open of the workbook.
Then when workbook is saved, this date and time remains static. Can this be
done?
 
Yes

Private Sub Workbook_Open()
Worksheets("Sheet2").Range("H1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

This would be pasted into the Thisworkbook module.

Right-click on the Excel Icon left of "File".

Select "View Code".............paste into that module.

Edit the Sheet2 to your liking.


Gord Dibben MS Excel MVP
 
The date timestamp changes on every workbook open. Is there a way to use a
command button or other method in which a user can either automatically or
manually freeze the time stamp?
 
The date timestamp changes on every workbook open. Is there a way to use a
command button or other method in which a user can either automatically or
manually freeze the time stamp?


Thanks
 
Hi

Take Gord's code but put it in a standard module in the workbook and
amend to

Sub DateTime()

Worksheets("Sheet2").Range("H1").Value = _
Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

Now this will appear as a macro in Tools>Macro>Macros> chose Options and
you can allocate a shortcut key to invoke the macro.

Alternatively, add a button to your toolbar and assign the macro to that
button.
Right click on a blank area of the toolbar,
Customise>Commands>Macros>drag the macro button to the toolbar>right
click>Assign Macro> point to DateTime
 
jk

You are correct. The stamp will update every time the workbook is opened.

Not what you wanted, I guess.

You want it to stamp the first time the workbook is opened then never again.

Forget the code.

Open the workbook and select H1.

CTRL + ; + space bar then SHIFT + ;

This gives a date & timestamp that never changes.


Gord
 
Yes. The code works and the custom button is cool too.If you rename the
file, it states file not found so is there a soulition for that and how do
you make the code work if you want to apply it to sheet 4?
 
Hi

If you want Sheet4, then change
Worksheets("Sheet2") to Worksheets("Sheet4")

or, if you wanted it to apply to whichever sheet you were working on
then

Sub DateTime()
ActiveSheet.Range("H1").Value = _
Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

or if you wanted it to apply to whichever cell you were in on whichever
sheet, then

Sub DateTime()
ActiveCell.Value = _
Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

The reason for the file not found error, is that when you attached the
macro to the button, it saved it with the Filename at that time plus the
macro name.
If you right click on the button>Customise>right click on the
button>Assign macro>use dropdown on Macros in to select>This Workbook
and point to the Macro name DateTime.

Alternatively, you could copy the macro to a module in your Personal.xls
file, point the button to the macro in Personal .xls, then it would
always be available to you.

For more help on installing macros David McRitchie has a wealth of
information on his site at

http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top