Can I program a macro in excel xp to apply a formula to any cell by pressing a short cut key?

Question

I have been trying to do this for weeks now. Want to add the formula =(Today()) to a macro and add a short cut to it. So that when I press the short cut it adds the current date to the current active cell. At present can only get it to apply the formula to a specific cell. Any ideas anyone???

Answer 1

In your macro use

ActiveCell.value = "Fred"

When you run the macro it will put Fred (or whatever you want) into the currently selected cell. By assigning a shortcut to the macro (TOOLS/MACRO/MACROS and Options) say T you could select the cell, press Ctrl + T and off you go.

Answer 2

That's really easy.

Go to:

Tools > Macro > Record New Macro

The Record Macro dialog box will come up. Type in the name you want for the macro. Then under where it says: "Store macro in:", I want you to pick "Personal Macro Workbook". This will allow you to use the macro every time you have Excel open. Then under "Shortcut key:" pick the shortcut letter that you want. Just make sure that it isn't a regular command shortcut already. For instance, you wouldn't want to use s (save), c (copy), or v (paste), b(bold), or i(italics). If you use an already exhisting shortcut it will over-ride the exhisting shortcut. So if you used c, then you couldn't use Ctrl + C to copy stuff.

Click OK on the Record Macro dialog box. Click the little blue square on the menu bar that pops up to stop recording the macro. You don't really want to record anything because you are going to open that macro up and enter the code by quickly typing it.

Now go to:

Tools > Macro > Macros...

This will bring up a list of the macros in all of the open workbooks. The one you want to select is the one you just created. It should have "PERSONAL.XLS!" in front of the name you chose for the macro. Select it by clicking it once, then click the Edit button.

Now enter the following line of code somewhere between the two lines with the Sub...End Sub statements. Probaby after the green note text that the Macro Recorder usually adds would be a good spot. Enter this:

ActiveCell.FormulaR1C1 = "=TODAY()"

Then in the upper left hand corner of the Visual Basic Editor window click the Save button (the button that looks like a 3.5" floppy disk). Then you can close the Visual Basic Editor window and you are done.

And WALLA!, what you been trying to do for weeks you just probably did in about 30 seconds to a minute.

PS - Here's an alternative that might work better. The TODAY formula always enters the current day's date into the Excel worksheet. Whenever you open Excel it will update the date for that cell which has that formula to that day's current date. If you don't want that date to change later you might want to use this as your code instead:

ActiveCell.Value = Format(Now, "mm/dd/yyyy")

You can replace the mm/dd/yyyy format with other types of date formats to get the exact desired output you want. But you'll notice that this puts the date and not a formula into the cell. Since it puts the actual date in as the value and not a formula, you don't have to worry about the date updating on you.

Answer 3

You don't need a macro. Once you type the formula into a cell, it automatically updates, that's what formulas do! But a quicker way of putting the current date in any cell is to press Ctrl ; on the keyboard and you have the date instantly without need for a formula. Ctrl: would give you the time only. Unnecessary to have a macro which you would assign to a shortcut key as you can't get shorter than what I've just told you.

Answer 4

I'm not sure if I understand your q correctly. If you start recording the macro, click on a blank cell, type in '=(TODAY())' and then stop recording. Insert a custom button to your toolbar and asign the macro to it. Then whenever you are in a cell and you click the custom button, the date will appear.