date problem

[d0wnt0wn]

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?

 

[andyh69uk]

Hi try this, code needs to be under ThisWorkbook

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]

Heres another option:-

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]

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]

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

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]

Hi D0wnt0wn:

Let me see if I have correctly understood what you are trying to accomplish ...

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]

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