date problem

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
hi I use an =today() formula in lets say cell a1... I want to know if its possible that if that sheet gets saved as something other than estimates.xls can the date formula be turned off so that it displays the date it was saved and not the current date?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

andyh69uk

New Member
Joined
Nov 23, 2005
Messages
31
Hi try this, code needs to be under ThisWorkbook

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Worksheets("Sheet1").Range("A1").Value = Date
End Sub

This will set A1 if the saveas window is brought up

However this does not mean the file was saved as something else

Andy
 

andyh69uk

New Member
Joined
Nov 23, 2005
Messages
31
Heres another option:-

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.FullName <> "C:\filename.xls" Then
    Worksheets("Sheet1").Range("A1").Value = Date
    ActiveWorkbook.Save
End If
End Sub

This option checks the filename before closing and sets A1 to the date if it doesn't match whatever you set, however it also has to save it so if the user wants to discard changes this will take that option away.

Andy
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
thanks for the reply....... would this resave the date every time i open the file and save it or will it just do it once?????... i would like this code to run only the first time that the workbook is saved and i only want it to run if the file has been saved as something other than estimate.xls
 

andyh69uk

New Member
Joined
Nov 23, 2005
Messages
31

ADVERTISEMENT

Hi there,

Right this code will check if the file is the original file (by that I mean the filename set in the code)it then checks for the date and if it says modified then date. If it isn't the original file and the date doesn't say "modified" date then it will change A1 to read eg Modified 24/11/2005

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.FullName <> "C:\filename.xls" And Left(Worksheets("sheet1").Range("A1").Value, 8) <> "Modified" Then
    Worksheets("Sheet1").Range("A1").Value = "Modified " & Date
    ActiveWorkbook.Save
End If
End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi D0wnt0wn:

Let me see if I have correctly understood what you are trying to accomplish ...
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ActiveWorkbook.Name = "estimates.xls" Then
        [a1] = "=today()"
        Else: [a1] = Date
    End If
End Sub

Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "estimates.xls" Then Range("A1").Formula = "=today()"
End Sub
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
thanks again guys.... i was just thinking.... if i save the file under a different name then all i need is a code that will put todays date in estimate xls then if the file i have save is not estimate.xls then do nothing because the original file date was saved when i saved the spreadsheet under a different name


so is it like this?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Name = "estimates.xls" Then
[a1] = "=today()"
End If
End Sub


is that how that goes or is there something that will cause an error if it is not named estimate.xls?

Private Sub Workbook_Open()
If ActiveWorkbook.Name = "estimates.xls" Then Range("A1").Formula = "=today()"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,536
Messages
5,572,767
Members
412,482
Latest member
arooshrana2
Top