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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,502
Members
449,944
Latest member
parag385

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
Back
Top