Date macro
 

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.

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

"Hiking" wrote in message
...
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.

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Hiking" wrote in message ...
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.

Just use the built-in feature of
ctrl + ; for date and : for time

--
Don Guillett
SalesAid Software

"Hiking" wrote in message
...
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.

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...

















"Paul B" kirjoitti
...
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

"Hiking" wrote in message
...
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.

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

"EM" wrote in message
...
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...

















"Paul B" kirjoitti
...
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

"Hiking" wrote in message
...
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.

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.

???

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...



"Hiking" kirjoitti
l.com...
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.

???

If you want to do it in a macro just use
range("a1")=date

--
Don Guillett
SalesAid Software

"EM" wrote in message
...
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...



"Hiking" kirjoitti
l.com...
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.

???

Hiking

ToolsCustomizeToolbars. 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

On Wed, 02 Feb 2005 13:26:39 -0500, Hiking
wrote:

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.

???