Fix Autodate in Excel? 1

[Hakala]

Hi, Tek-Tippers!

I have a worksheet in Excel 2002 which has a date field that autofills with today's date. We use this as a form, and the users were often forgetting to update the date, so we autofilled it.

The trouble is, it autofills every time the sheet is opened.

What I need is a sheet that autofills today's date when it's opened the first time, and then fixes that date when the sheet is saved with another name.

Any ideas?

(I posted this in the MS Office forum, and they directed me here.)

Michelle Hakala

http://www.winebird.com/

 

[PHV]

You may consider the BeforeSave event procedure of the Workbook.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

-> and then fixes that date when the sheet is saved with another name.

My first thought is to use the BeforeSave event.

This would trigger code to put the current date (hard coded, not a dynamic formula) into a cell when the user saves the file.

Would that work for your needs?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Hakala]

It sounds like the BeforeSave event would work really well, but I don't know how to set it up.

I have done some tinkering with VBA in Access.

Michelle Hakala

http://www.winebird.com/

 

[AnotherHiggins]

With the WorkBook you want to make the changes to open, press [Alt] + [F11].

On the left, you should see a pane that resembles Windows Explorer. (If not, press [Ctrl]+[R])

Look for VBAProject([Your File Name]).

Double Click on ThisWorkbook under Microsoft Excel Objects.

In the right pane (where code goes), at the top left corner, change the drop down from General to Workbook.

Change the top right drop down from Open to BeforeSave.

That will generate an outline that looks like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

In the middle, paste one of the following:
For the current date and time:
[tab][A1] = Now())

For the current date:
[tab][A1] = Int(Now())

(change [A1] to whatever cell you want)

You can tweak the formatting of the cell if you want.

The end result will look something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    [A1] = Int(Now())
    [A1].NumberFormat = "yyyy-mm-dd"
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Hakala]

It worked! Have a star, for explaining it so that I could implement it.

Michelle Hakala

http://www.winebird.com/