Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to edit formula without changing formula of each cell | Excel Worksheet Functions | |||
Changing cell locations with formula? | Excel Discussion (Misc queries) | |||
changing cell colors with formula | Excel Worksheet Functions | |||
formula with changing cell ranges | Excel Worksheet Functions | |||
A cell reference in a formula changing | Excel Worksheet Functions |