changing a cell formula to value before save
 

when i enter the =now() formula in a cell, i can have it changed to the
absolute value upon saving the file (or upon opening). but i need the
formula in the template file in order to display the current date and time
every time it opens, but as soon as the template file is saved - or opened -
(with the VB script for changing the value) the formula changes to absolute?
its a stupid question, I know, but are there any suggestions?

changing a cell formula to value before save
 

Press Alt + F11 to open the VBE and in the Project Explorer window in the
upper left, double click on the ThisWorkbook icon to open the workbook module.

In the code window on th e right there are 2 combo boxes at the top of the
window. In the first drop down select Workbook and in the second drop down
select Open. Add the following code, changing Sheets1 to the worksheet you
want the date posted in and Range("A1") to the cell address you want the data
placed in:

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)

ws.Range("A1").Value = Date

Your code should look like this when you're done:

Private Sub Workbook_Open()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)

ws.Range("A1").Value = Date

End Sub
--
Kevin Backmann


"des-sa" wrote:

when i enter the =now() formula in a cell, i can have it changed to the
absolute value upon saving the file (or upon opening). but i need the
formula in the template file in order to display the current date and time
every time it opens, but as soon as the template file is saved - or opened -
(with the VB script for changing the value) the formula changes to absolute?
its a stupid question, I know, but are there any suggestions?

changing a cell formula to value before save
 

kevin,
many thanks for that. i already have this code in the workbook open module:
Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "QUOTE1"
Const sKEY As String = "QUOTE1_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("QUOTE")
With .Range("F8")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("K2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

do i add your's? where

"Kevin B" wrote:

Press Alt + F11 to open the VBE and in the Project Explorer window in the
upper left, double click on the ThisWorkbook icon to open the workbook module.

In the code window on th e right there are 2 combo boxes at the top of the
window. In the first drop down select Workbook and in the second drop down
select Open. Add the following code, changing Sheets1 to the worksheet you
want the date posted in and Range("A1") to the cell address you want the data
placed in:

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)

ws.Range("A1").Value = Date

Your code should look like this when you're done:

Private Sub Workbook_Open()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)

ws.Range("A1").Value = Date

End Sub
--
Kevin Backmann


"des-sa" wrote:

when i enter the =now() formula in a cell, i can have it changed to the
absolute value upon saving the file (or upon opening). but i need the
formula in the template file in order to display the current date and time
every time it opens, but as soon as the template file is saved - or opened -
(with the VB script for changing the value) the formula changes to absolute?
its a stupid question, I know, but are there any suggestions?