code for date help

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

How about this?

THe code needs to go into the THIS WORKBOOK code window

Code:
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

Well-known Member
Joined
Oct 28, 2002
Messages
771
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

for the first part try

Code:
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
how about?
Code:
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

Well-known Member
Joined
Oct 28, 2002
Messages
771
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.
 

Forum statistics

Threads
1,136,621
Messages
5,676,852
Members
419,656
Latest member
lironprofit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top