MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Icon for putting DATE into cell


Posted by Eli Weiss on November 20, 2001 10:34 PM

Hello,
Is there any way to make/use icon on toolbar
which will put date into the current cell?
Thank you in advance
Eli


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

Sub Date()
'
' Macro2 Date
'

'
Sheets("Sheet1").Select
Range("B1").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

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.

HTH

Regards,

Gary Hewitt-Long

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.

HTH

Regards,

Gary Hewitt-Long

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?

Much appreciation,
Eli

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.

Regards,

Gary Hewitt-Long