Date macro

H

Hiking

Hello all,

I have a column named Last Update, into which I want to be able to click
on a row and press a key combination and have today's date automatically
stamped in this cell.

I created a macro, it inserts "=today()" in the current active cell,
which is fine, I execute my macro and see today's date in the cell.
Problem is that when I look at that cell tomorrow, it will no longer
have yesterday's date in it.

Somehow I need the function to insert in that cell the value of the
formula, and not the formula itself, so's when I open the sheet in the
future, I will see in the cell the date when the macro was executed.

I'm fairly certain this is a very simple thing to do, but as anyone
who's tried to make use of Excel's help knows, there is very little
usable information here. I've searched the net, but always find how to
do everything else but what I specifically need.

I tried putting the =today() formula somewhere else in the sheet so as I
could copy and then paste the value of the formula only, but I need to
edit the macro so as it won't paste the value to the cell that was
active at the time I created the macro. If you're not familiar with VB,
this is no small feat.

To make matters MUCH WORSE, I was going to try recording the macro with
the "Relative" button depressed on the small Macro toolbar which appears
when I would be recording a macro, but I made the mistake of trying to
cancel a macro recording by clicking on the "X" close button of this
small toolbar. Now when I'm recording macros, I don't have this toolbar
pop up any more, along with the "Relative" button that was on this
toolbar. Anyone of you daring enough to try this in order to attempt to
find this little Macro toolbar over again? Careful. I've looked all
over the "Customize" window, but cannot find this "Relative" button
anymore. Where is it???

Any assistance is greatly appreciated. Thank you.
 
P

Paul B

Hiking, use date instead of =today() , something like ActiveCell = Date
To get the stop button back, when you are recording a macro go to view
toolbars and stop recording
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
R

Ron de Bruin

You can use a Shortcut for it

you can insert the time like this
CTRL : (colon)

the date like this
CTRL ; (semicolon)

CTRL : (colon) space bar CTRL ; (semicolon)
this will give you both in one cell
 
E

EM

I've been struggling with the same problem...
didn't quite understand what "To get the stop button back, when you are
recording a macro go to view
toolbars and stop recording" means...
 
P

Paul B

EM, start recording a macro then click on the view menu, then toolbars and
click on stop recording, this will put the stop recording button back on
your sheet
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
H

Hiking

Ron de Bruin and Don Guillett have the simple solution I was looking for.

I spent countless hours, search after search, scouring web site after
web site for just this solution, but never in all the sites and pages
did I run across just this, just what I was looking for. Unbelievable.
I knew there had to be something this simple, I knew this I was trying
to do had to be a basic, fundamental, essential element/function in/of
Excel, but damnit, nowhere was I finding it. Thanks all.

As for the macro problem, I am still interested in getting that small
toolbar back, if anyone knows how. I have gone to the Customize window,
and put on my toolbars on top the Start and Stop macro buttons, but just
cannot find that "Relative" button that used to be on that toolbar.
This toolbar would pop up on top of the spreadsheet when I went Tools >
Macro > Record New Macro... Now that toolbar doesn't appear anymore,
and while I have found macro-related Start Stop buttons in the Customize
window, I can't seem to find anywhere that "Relative" button that used
to appear with that pop-up toolbar I've just mentioned above.

???
 
E

EM

Well what don't I understand?
Everytime you update something? That means hundreds of times a day for me??
Copying the cell doesn't do that...
 
G

Gord Dibben

Hiking

Tools>Customize>Toolbars. Scroll down and find the Stop Recording Toolbar.

Click on it and "Reset" to get the Relative button back.

Make sure it is checkmarked then OK your way out.

Record a simple throwaway macro to make sure all is working correctly then hit
the Stop Recording button.

DO NOT close out using the X while recording or you will lose it again.


Gord Dibben Excel MVP
 
Top