How to select cell and have time or date appear?

[rdraper]

Hi all,

Need a little 'best method' help with a phonecall log,
where ColumnA is Date, ColumnB is Time.
I wanna be able to click on B2 and have the current time inserted, then
click on B3 and have another time inserted w/o changing B2.

I've know this has been discussed before, but Im still confused if a
macro is needed in this simple case.
Can an event trigger be worked into a formula function or must it be
vba?
In a previous thread it was suggested to Insert/Name/Define a cell
range with a macro or function name. Will this then trigger a macro to
(ctrl + shift +or (=Now),
or do I need to use something more like the excerpt below;


( "Mike Fogleman Mar 18, 4:00 am show options

Newsgroups: microsoft.public.excel.programming
From: "Mike Fogleman" <[email protected]> - Find messages by
this author
Date: Fri, 18 Mar 2005 07:00:57 -0500
Local: Fri,Mar 18 2005 4:00 am
Subject: Re: Run Macro When Select Cell
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Private Sub Worksheet_SelectionChange(ByVa­l Target As Range)
If Not Application.Intersect(Range("J­1:J100"), Target) Is Nothing
Then
'Call your Macro
Else
End If
End Sub


Change the Intersect Range to whatever cell or range you want as the
trigger.
This code goes in the Worksheet Module, not a general module.

Mike F ")

thanks,

bobd

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1:B100")) Is Nothing Then
With Target
.Value = Format(Now, "hh:mm:ss")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


Hi all,

Need a little 'best method' help with a phonecall log,
where ColumnA is Date, ColumnB is Time.
I wanna be able to click on B2 and have the current time inserted, then
click on B3 and have another time inserted w/o changing B2.

I've know this has been discussed before, but Im still confused if a
macro is needed in this simple case.
Can an event trigger be worked into a formula function or must it be
vba?
In a previous thread it was suggested to Insert/Name/Define a cell
range with a macro or function name. Will this then trigger a macro to
(ctrl + shift +or (=Now),
or do I need to use something more like the excerpt below;


( "Mike Fogleman Mar 18, 4:00 am show options

Newsgroups: microsoft.public.excel.programming
From: "Mike Fogleman" <[email protected]> - Find messages by
this author
Date: Fri, 18 Mar 2005 07:00:57 -0500
Local: Fri,Mar 18 2005 4:00 am
Subject: Re: Run Macro When Select Cell
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Private Sub Worksheet_SelectionChange(ByVa­l Target As Range)
If Not Application.Intersect(Range("J­1:J100"), Target) Is Nothing
Then
'Call your Macro
Else
End If
End Sub


Change the Intersect Range to whatever cell or range you want as the
trigger.
This code goes in the Worksheet Module, not a general module.

Mike F ")

thanks,

bobd

 

[rdraper]

thank you, works great!
Now I get it, how to trigger an event and apply a function.

thanks,
bobd

 

[Bob Phillips]

Bob,

Do understand that the Now function in this example is not the same as the
worksheet function NOW(). They basically do the same things, but when you
enter the latter in a worksheet, Excel will generally automatically format
it as time, but in VBA Now just returns a time serial value which you need
to format if outputting to a worksheet cell (as the code does).

This becomes more clear with today's date, the worksheet function is
TODAY(), the VBA equivalent function is Date.

As well as invoking the code in -line, you could call a macro once the
criteria are established.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[rdraper]

thank you, works great!
Now I get it, how to trigger an event and apply a function.

thanks,
bobd

 

[rdraper]

your points are well taken and timely.
I was just trying to add an else loop in line for Today() rather than
Date.
Still have some reading to do on your last point.

 

[rdraper]

The code works great, but is this the proper way to add a second if
statement?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B4:B100")) Is Nothing Then
With Target
.Value = Format(Now, "hh:mm:ss")
End With
End If
If Not Intersect(Target, Me.Range("A4:A100")) Is Nothing Then
With Target
.Value = Format(Date, "m/d/yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True


End Sub

 

[Doug Glancy]

That way will work, but in order to better indicate that they are mutually
exclusive, I'd turn the second if into an elseif - which is only evaluated
if the first if clause isn't True. Also, With statements are great, but
when there's only one method or property I'd eliminate them and shorten the
code, e.g.,:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B4:B100")) Is Nothing Then
Target.Value = Format(Now, "hh:mm:ss")
ElseIf Not Intersect(Target, Me.Range("A4:A100")) Is Nothing Then
Target.Value = Format(Date, "m/d/yyyy")
End If
ws_exit:
Application.EnableEvents = True

End Sub

 

[Bob Phillips]

Although it works, it is wasteful because both conditions get tested each
time. So, if the target range is within B4:B100, the cell is set to time,
but it then also tests for the target range being within A4:A100, which it
clearly cannot be.

Use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range("B4:B100")) Is Nothing Then
.Value = Format(Now, "hh:mm:ss")
ElseIf Not Intersect(Target, Me.Range("A4:A100")) Is Nothing Then
.Value = Format(Date, "m/d/yyyy")
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)