Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page Setup Across Multiple Worksheets | Excel Worksheet Functions | |||
Excel 2003 - PAGE SETUP - FIT TO WIDTH does not work | Excel Discussion (Misc queries) | |||
Excel; how do I print titles in 'Setup' when option greyed out? | Excel Worksheet Functions | |||
Is there a way to apply "page setup" defaults to an existing work. | Excel Discussion (Misc queries) | |||
How do I copy page setup from one worksheet & paste into new shee. | Excel Discussion (Misc queries) |