How do i setup a 'toggle'

T

Tedwards

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!!
 
B

Bob Phillips

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)
 
B

broro183

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 t
appear. This will update each time the workbook is calculated eg when i
is opened.


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

Option 2 (macro based):
This will insert the date in the (same) cell of your choice on of eac
worksheet in your workbook when it is opened.
Press [alt + F11], then press [ctrl + r], double click on th
"thisWorkbook" icon which appears in the left panel under the file yo
are working in, & copy the below code into the window that should ope
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 th
"appropriate sheet" icon which appears in the left panel under the fil
you are working in, & copy the below code into the window that shoul
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 entere
in. It also relies on the format of the cell being correct before th
macro is run.


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