Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2016, 11:38 AM
ntbluez ntbluez is offline Date/Time Entry Windows 7 32bit Date/Time Entry Office 2013
Novice
Date/Time Entry
 
Join Date: Mar 2016
Posts: 1
ntbluez is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-06-2016, 10:12 PM
Philb1 Philb1 is offline Date/Time Entry Windows 10 Date/Time Entry Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

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
Reply With Quote
Reply

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 Entry 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
Date/Time Entry Sent emails all have same date/time?? Confused Outlook 3 01-07-2011 07:52 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft