#1
|
|||
|
|||
Date/Time Entry
I'm trying to write excel vba and result is half-way working.
Its intention is to enter a date/timestamp in column C for respective row in column B. This occurs when clicking on any cell in column B. It is suppose to do this for columns D and E where E is the date/time entry for any cell clicked on in column D. EXAMPLE... If I click on cell B5, date/time stamp should appear in C5. If I click on cell D20, a date/timestamp should appear in C20. I hope this helps you to understand.***ALSO, Top Row is a lable, like A1 has word Task#, B1 has word like StepsToDo, C1 has word like Time4Steps,... I want Top Row to not accept any new text - protected ***. Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iRange As Range If Target.Cells.Count <> 1 Then Exit Sub Set iRange = Intersect(Target, Range("B:B")) If iRange Is Nothing Then Exit Sub Application.EnableEvents = False Range("C" & Target.Row).Value = Now() Application.EnableEvents = True Dim xRange As Range If Target.Cells.Count <> 1 Then Exit Sub Set xRange = Intersect(Target, Range("D:D")) If xRange Is Nothing Then Exit Sub Application.EnableEvents = False Range("E" & Target.Row).Value = Now() Application.EnableEvents = True End Sub |
#2
|
|||
|
|||
Hi
Your code has separated the two column routines completely. It worked if you clicked column B, but if you clicked column D, it checked if column B was the target and if it wasn't the whole routine terminated. Here is how I would do it Cheers Code:
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim BCol As Range, DCol As Range Set BCol = Range("B:B") Set DCol = Range("D:D") ' If any errors occur make sure Enable Events is on when macro exited On Error GoTo ExitOut If Not Intersect(Target, BCol) Is Nothing Or Not Intersect(Target, DCol) Is Nothing Then Application.EnableEvents = False If Target.Row >= 2 Then ' Minimum of row 2 With Target.Offset(, 1) ' Put date & time in adjacent column to the right .Value = Now .NumberFormat = "dd/mm/yyyy hh:mm:ss" ' Change to suit End With GoTo ExitOut End If End If GoTo ExitOut ExitOut: On Error GoTo 0 Application.EnableEvents = True Exit Sub End Sub |
Tags |
date/time, vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Day date and time cell display | markg2 | Excel | 2 | 02-11-2015 06:57 AM |
date & time of last time a recent file was used | pgeorgilas | Word | 1 | 10-31-2014 08:59 AM |
Date/Time Formula for Entry level XL user | talon1driver | Excel | 2 | 09-18-2014 02:32 PM |
Count the common time period (month) between two date period of time | Barni | Excel | 6 | 08-15-2014 07:52 AM |
Sent emails all have same date/time?? | Confused | Outlook | 3 | 01-07-2011 07:52 AM |