Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
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" - Find messages by this author Date: Fri, 18 Mar 2005 07:00:57 -0500 Local: Fri,Mar 18 2005 4:00 am Subject: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
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) wrote in message ups.com... 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" - Find messages by this author Date: Fri, 18 Mar 2005 07:00:57 -0500 Local: Fri,Mar 18 2005 4:00 am Subject: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
thank you, works great!
Now I get it, how to trigger an event and apply a function. thanks, bobd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
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) wrote in message oups.com... thank you, works great! Now I get it, how to trigger an event and apply a function. thanks, bobd |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
thank you, works great!
Now I get it, how to trigger an event and apply a function. thanks, bobd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cell and have time or date appear?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select onlu one cell at a time? | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |