How do i setup a 'toggle'
 

I would like to have the date setup on my excel spreadsheets so when the
sheet is opened the dated automatically stes itself to that days date. I
have set it up in word using the toggle mode, but cannot locate the same mode
in excel.

Can anyone hel me as it's drivin me nuts!!

How do i setup a 'toggle'
 

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Worksheets("Sheet1").Range("A1").Value = Date
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tedwards" wrote in message
...
I would like to have the date setup on my excel spreadsheets so when the
sheet is opened the dated automatically stes itself to that days date. I
have set it up in word using the toggle mode, but cannot locate the same
mode
in excel.

Can anyone hel me as it's drivin me nuts!!

How do i setup a 'toggle'
 

Hi,
No need to go nuts! :-)

Option 1 (formula based - simple & effective):
This should be exactly what you are after - try entering,
"=today()"
into a suitable cell & then formatting the cell as you want the date to
appear. This will update each time the workbook is calculated eg when it
is opened.


I was just having a play & came up with the following macro based
option which does the same job but is made redundant by the ease of use
of the "today" function (above)...

Option 2 (macro based):
This will insert the date in the (same) cell of your choice on of each
worksheet in your workbook when it is opened.
Press [alt + F11], then press [ctrl + r], double click on the
"thisWorkbook" icon which appears in the left panel under the file you
are working in, & copy the below code into the window that should open
in the right panel...

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim DateFormatToUse As String
Dim DateCell As Range
For Each ws In ActiveWorkbook.Worksheets
Set DateCell = ws.Range("G8")
DateFormatToUse = DateCell.NumberFormat
With DateCell
.Value = Date
.NumberFormat = DateFormatToUse
End With
Next ws
End Sub

Or if you just want it in certain sheets of your file...
Press [alt + F11], then press [ctrl + r], double click on the
"appropriate sheet" icon which appears in the left panel under the file
you are working in, & copy the below code into the window that should
open in the right panel...

Private Sub Worksheet_Activate()
Dim DateFormatToUse As String
Dim DateCell As Range
Set DateCell = Range("G8")
DateFormatToUse = DateCell.NumberFormat
With DateCell
.Value = Date
.NumberFormat = DateFormatToUse
End With
End Sub

NB: you can change the "G8" to any cell which you want the date entered
in. It also relies on the format of the cell being correct before the
macro is run.


Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=517197