Time Stamp

[Papi]

How can I have a template open and have hhmm entered into a cell i.e.if =NOW() was 8/6/2011 12:03 then I would like 1203 to remain in that cell to be used as a four digit code from that time forward?

 

[jim may]

This works... Paste into the Thisworkbook module

Private Sub Workbook_Open()
Range("A1").NumberFormat = "hh:mm"
Range("A1").Formula = "=Now()-Today()"
Range("B1").Formula = "=Text(A1,""hhmm"")"
Range("B1").Value = "'" & Range("B1").Value
Range("A1").NumberFormat = "@"
Range("A1").Value = Range("B1").Value
Range("B1").ClearContents
End Sub

 

[VoG]

Or

Private Sub Workbook_Open()
Range("A1").Value = Time
End Sub

 

[Jerry Sullivan]

If you are wanting the time stamp to be static after running once, you could try this alternative, which is slightly different that Jim's.

Private Sub Workbook_Open()
    If Sheets("Sheet1").Range("A1") <> vbNullString _
        Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
    With Sheets("Sheet1").Range("A1")
        .NumberFormat = "0"
        .Value = Format(Now(), "HHMM")
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Papi]

Am I supposed to right click the tab button and insert into view code? I cannot seem to find "Thisworkbook module" so am not sure what I am doing wrong or where to put the code listed from the three results. The best I can do is normally run the macro recorder and the odd time modify some of that stuff but am not so adept at working with vba code.

 

[Jerry Sullivan]

Am I supposed to right click the tab button and insert into view code? I cannot seem to find "Thisworkbook module" so am not sure what I am doing wrong or where to put the code listed from the three results. The best I can do is normally run the macro recorder and the odd time modify some of that stuff but am not so adept at working with vba code.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

 

[VoG]

Press ALT + F11, in the Project window double click ThisWorkbook. Paste the code in there.

 

[Papi]

Thanks folks. What is the difference between doing it that way and inserting it in the view code?

 

[VoG]

Right clicking a tab and selecting View Code gets you into the worksheet's code module. You actually want the workbook's code module which is different.

 

[Papi]

Thanks Peter. Now is see why I have had problems in the past. i'll read up on the two this weekend.