Icon for putting DATE into cell
Posted by Eli Weiss on November 20, 2001 10:34 PM
Is there any way to make/use icon on toolbar
which will put date into the current cell?
Thank you in advance
Posted by Gary Hewitt-Long on November 21, 2001 12:17 AM
Have a cell somewhere out of the way with the formula =TODAY() in it A1 for example. Have another cell that references that cell with the formula =TEXT(A1,"dd/mm/yy") This may be in cell B2
Use the following Macro
' Macro2 Date
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
Replace Sheet2 in the macro with the name of the sheet you will be pasting into. You can change this so it will work on any sheet by using changing to activeSheet.
Posted by Gary Hewitt-Long on November 21, 2001 12:19 AM
Nearly Forgot, to add a button, right click on your toolbar, customise, goto Macros, drag a custom button to the toolbar and then click on assign macro. Choose the macro that puts the date in.
Posted by Eli Weiss on November 21, 2001 3:20 AM
Thank you Gary for your nice solution
Still 2 remarks
1-macro like the following dose the same:
ActiveCell.FormulaR1C1 = "=TODAY()"
2-How can I make the macro available in every new workbook without copying it always from another one?
Posted by Gary Hewitt-Long on November 21, 2001 11:00 AM
Old habit, I tend to get people changin things I have done who don't know VBA, it's easier to let them change date format by editing the format within the worksheet rather than trying to edit the VBA.
Store the macro in your personal workbook, which excel will open on startup. Should then be available in any worksheet.
If you are using the date on the worksheet you will then need to make sure that the macro references that workbook as well.