Saving today's date in a cell

[pullingteeth]

Hello, I'd like to be able to save today's date in an excel worksheet, preferably using a cell formula. To be clear, when I open the spreadsheet tomorrow, it should still have today's date (the date that I saved it) in the cell.

Thanks

 

[SkipVought]

Hi,

You can use the Worksheet_SelectionChange event to insert today's date IF the cell is empty

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   With Target
      If .Count > 1 Then Exit Sub
      If .Row = 1 Then Exit Sub
      Set rng = Application.Intersect(Target, Range("H:H"))
      If Not rng Is Nothing Then
         If .Value = "" Then
            .Value = Now
         End If
      End If
   End With
End Sub

If you just want the date instead of date/time then substitute Date for Now


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

BTW,

The code puts the date in column H. Choose whatever range you want.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[pullingteeth]

Hi Skip, I'm playing with the code right now and can't figure out how it is triggered. Could you clue me in?

Thanks
(Sorry, I'm a noob when it comes to excel/vba; I'm pretty comfortable in access/vba, however.)

 

[pullingteeth]

Actually, the "real" question is: I keep getting Type Mismatch errors. Any idea why?

Thanks

 

[VBAjedi]

Also, you can insert a static representation of todays date into the active cell with a simple key shortcut:

Ctrl + ; (semicolon)

The current time can be similarly inserted:

Ctrl + Shift + : (colon)

Useful shortcuts to remember. . .

VBAjedi

 

[SkipVought]

I posted in haste. I should have advised you that the code goes into the Worksheet object - right click the sheet tab gets you there.

There are several events that can be trapped (used) in excel VBA like Worksheet_Change, Worksheet_SelectionChange, Worksheet_Activate, Workbook_Open just to name a few.

When you change a selection, the Worksheet_SelectionChange event fires. If it has code, like I posted, then it will process.

If you just wanted a single cell to have the date, then change the range in

Set rng = Application.Intersect(Target, Range("H:H"))

Then, whenever you Select in that range, the date will be assigned to that cell.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[ETID]

'or maybe this....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("sheet1").Range("a1") = Date$ 'change sheet and range as needed
End Sub

 

[DaleWatson123321]

Hi guys,

I'd respectfully suggest that the "Workbook BeforeSave" event would be the best to use.

This event will ensure the "real" date saved is "preserved" - i.e. not affected by the other events which could change the actual date last saved.

The following code should be placed under "ThisWorkbook".

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
curtime = Now()
Range("dtsaved").Value = curtime
End Sub

The "dtsaved" is a range name assigned to a cell (that can be on any sheet in the workbook).

I have the cell formatted with the following custom date format:
m/d/yyyy h:mm AM/PM

I hope this helps

Regards, Dale Watson dalwatson@gov.mb.ca

 

[Loomah]

You could also try this UDF

Function LastSaved() As Date
Application.Volatile
LastSaved = ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value
End Function

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

Soooooooo many ways to skin a cat!

Ain't it great!

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}