eXCEL

[Lenny]

Can I set up as an excell tool bar on my worksheet a shortcut to format the date for cells... mmo/dd/yy....
Can format "," for decimal place value number, but what about a date format shortcut as an excell tool.

Thanks for help if you can....
Will save much work when formatting cells in my spreadsheets.

Lenny Walder


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ed]

From the Excel Help files: Keyboard Shortcuts:
CTRL+SHIFT+#
Apply the Date format with the day, month, and year.

Will that do for you?

Ed
"F1 does _not_ crash your computer!"

 

[jbutler]

You can. Just record a macro to format the date, add a button to the toolbar, and assign the macro to the button. So,
1)Record the macro:
-Click on a cell you want to format.
-Go to Tools > Macro > Record New Macro
-Type a name for your Macro, with no spaces, like "FormatDate"
-Click the drop down box to store the macro in your personal macro workbook. That way, it will be available in all your excel files.
-Hit Ok, and the macro recording begins.
-Format the date the way you normally would, using format > cells. Make sure you don't select any cells during the macro recording. Just do the formating.
-Hit the stop recording button.

2)Add a button to your toolbar:
-Right click in the toolbar area, and choose Customize
-Click the commands tab
-In the categories box on the left, choose Macros
-In the commands box, drag either a custom button or custom menu item up and put it on one of your toolbars.

3)Assign the macro to your new buton.
-Click the Modify Selection, and choose Assign Macro.
-In the box that pops up, choose the macro you just recorded. If you don't see it, try changing the drop down box at the bottom.
-Hit OK, then Close.

Then give your new button a try. If it doesn't work as expected, try looking at the code for the macro by going to Tools > Macro > Macros. Then click your macro and choose Step Into. The code only needs to be a few lines, like this:

Sub FormatDate()
Selection.NumberFormat = "mm/dd/yy"
End Sub

If you have too many other lines, it may be because you did other things while the macro was recording, and that can throw off your results.

Let me know if this helps.
Joe

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[LENNY]

Joe..

Thanks for your response....

I will try your suggestion later today....

Really appreciate your full comprehensive response...

This is the first time I have used this web site and not sure how responses are handled like this one.

Lenny


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[David McRitchie]

Hi Lenny,
Depending on what you actually need you might be able to
complete do away with your need for a toolbar button.

There is the shortcut key Ctrl+(semicolon)

and you can have a date placed automatically with an event
macro on a per worksheet basis.
DateTimeStamp in Column A, on first entry in any other column on row
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestamp
for the code, but you should read the webpage from the beginning.

Your question was redirected to a newsgroup, and answered in a
newsgroup. Most web interfaces are specifically designed to
disrupt distinctions between websites and newsgroups searches
to serve advertisements. You would be better off posting directly
to a newsgroup rather than through a web page.
http://www.mvps.org/dmcritchie/excel/xlnews.htm