Date/Time Entry

ntbluez · #1 03-06-2016, 11:38 AM

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

Philb1 · #2 03-06-2016, 10:12 PM

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

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