code for date help

[d0wnt0wn]

Hi there I was wondering if someone might be able to help me with a little code for dating my workbook

basically i have =today() in c3 in my workbook named estimate.xls but after i do the estimate and save the file under a different name i would like to erase the formula and just show the date... any ideas?

 

[onlyadrafter]

Hello,

How about this?

THe code needs to go into the THIS WORKBOOK code window

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("SHeet1").Range("C3").Copy
Sheets("SHeet1").Range("C3").PasteSpecial (xlValues)
End Sub

change refs as required

 

[d0wnt0wn]

that looks like it will work with the exception that if the workbook is named estimate.xls i dont want the code to kick in.

Also.. If i adjust the estimate at a later date and i manulally enter the new date the code will kick in and overwrite my manual entry...

hope that made sense

 

[onlyadrafter]

Hello,

for the first part try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.Name <> "estimate" Then
    Sheets("SHeet1").Range("C3").Copy
    Sheets("SHeet1").Range("C3").PasteSpecial (xlValues)
End If
End Sub

sorry, dont understand the second part.

 

[agihcam]

how about?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.Name <> "estimate" Then
    If Range("c3").HasFormula = True Then
        Range("c3").Value = Date
    End If
End If
End Sub

 

[d0wnt0wn]

both of your suggestions are very good and i tried them both to see how it would work but here is the road block i am running into....

i start with a workbook named estimate.xls in c3 there is a formula =today() which i like... but i have a macro that takes the address info from c5 and saves the file as that address when i click on a save button... so a before save does not work.... i think i have to have a before close kind of thing rather than a before save but i couldnt figure it out.