I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

You could use something like:

Option Explicit
Sub PutDateTime()
On Error Resume Next
With Selection
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
If Err.Number < 0 Then
MsgBox "Time not inserted"
Err.Clear
End If
On Error Goto 0
End Sub

If you give it a nice shortcut combo, it might even be easier.

Tools|Macro|macros
select PutDateTime
click options
Change the shortcut key Uppercase T (maybe???)
When you type that uppercase T, the dialog will show
ctrl-shift-T
click ok
click cancel to dismiss the dialog

Try it out.

PrologPro wrote:

I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

--

Dave Peterson

Unfortunately that didn't work. When I run the macro, no value is entered.
I also need to make sure the date and time are static and automatically
entered with a macro (instead of entering today's date and time manually),
which is why I wanted to avoid the NOW formula.

I wish there were a way to record a macro or write VB for the "CTRL + ;"
shortcut...any ideas?

Thanks!

"Dave Peterson" wrote:

You could use something like:

Option Explicit
Sub PutDateTime()
On Error Resume Next
With Selection
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
If Err.Number < 0 Then
MsgBox "Time not inserted"
Err.Clear
End If
On Error Goto 0
End Sub

If you give it a nice shortcut combo, it might even be easier.

Tools|Macro|macros
select PutDateTime
click options
Change the shortcut key Uppercase T (maybe???)
When you type that uppercase T, the dialog will show
ctrl-shift-T
click ok
click cancel to dismiss the dialog

Try it out.

PrologPro wrote:

I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

--

Dave Peterson

Hi PrologPro,

Unfortunately that didn't work. When I run the macro, no value is
entered.

Dave's macro worked for me, inserting the current date and time in the
selected cell(s).

I also need to make sure the date and time are static and automatically
entered with a macro (instead of entering today's date and time manually),
which is why I wanted to avoid the NOW formula.

Dave's macro inserts a *statiic' snapshot date/time value.

I did not test Dave's shortcut suggestion, but I have no reason to assume
that this would not work perfectly.


---
Regards,
Norman



"PrologPro" wrote in message
...
Unfortunately that didn't work. When I run the macro, no value is
entered.
I also need to make sure the date and time are static and automatically
entered with a macro (instead of entering today's date and time manually),
which is why I wanted to avoid the NOW formula.

I wish there were a way to record a macro or write VB for the "CTRL + ;"
shortcut...any ideas?

Thanks!

"Dave Peterson" wrote:

You could use something like:

Option Explicit
Sub PutDateTime()
On Error Resume Next
With Selection
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
If Err.Number < 0 Then
MsgBox "Time not inserted"
Err.Clear
End If
On Error Goto 0
End Sub

If you give it a nice shortcut combo, it might even be easier.

Tools|Macro|macros
select PutDateTime
click options
Change the shortcut key Uppercase T (maybe???)
When you type that uppercase T, the dialog will show
ctrl-shift-T
click ok
click cancel to dismiss the dialog

Try it out.

PrologPro wrote:

I'm trying to come up with the quickest way to enter the current date
and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date,
it
actually has today's date listed in VB, not the actual function. I
just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep
track of
patches I've applied, and the date and time will change with each row
entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

--

Dave Peterson

A simple sendkeys command may do the trick.

Sub Macro1()
' use with the range statement to specify a location or without to place the
value in
'the activecell
Range("B25").Select

SendKeys "^{;}"

End Sub

hth

DMoney
--
EzMoney


"PrologPro" wrote:

I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

How about?

Sub DateTime()
Selection.Value = Now
End Sub

Will enter the current date/time and does not change.
Stefan

PrologPro wrote:
I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

What happened when you tried it?



PrologPro wrote:

Unfortunately that didn't work. When I run the macro, no value is entered.
I also need to make sure the date and time are static and automatically
entered with a macro (instead of entering today's date and time manually),
which is why I wanted to avoid the NOW formula.

I wish there were a way to record a macro or write VB for the "CTRL + ;"
shortcut...any ideas?

Thanks!

"Dave Peterson" wrote:

You could use something like:

Option Explicit
Sub PutDateTime()
On Error Resume Next
With Selection
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
If Err.Number < 0 Then
MsgBox "Time not inserted"
Err.Clear
End If
On Error Goto 0
End Sub

If you give it a nice shortcut combo, it might even be easier.

Tools|Macro|macros
select PutDateTime
click options
Change the shortcut key Uppercase T (maybe???)
When you type that uppercase T, the dialog will show
ctrl-shift-T
click ok
click cancel to dismiss the dialog

Try it out.

PrologPro wrote:

I'm trying to come up with the quickest way to enter the current date and
time in my spreadsheet.

So far here's what I've tried:

Macro which recorded CTRL + ; (space) CRTL + SHIFT + ;
Unfortunately if you look at how the macro recorded this static date, it
actually has today's date listed in VB, not the actual function. I just want
VB to perform the FUNCTION.

I also tried the NOW() function, but I will have multiple rows keep track of
patches I've applied, and the date and time will change with each row entry.
The NOW formula updates all rows with the same date and time.

Any suggestions would be greatly appreciated.
Thanks!

--

Dave Peterson


--

Dave Peterson