Closed Thread
Results 1 to 10 of 10

Timestamps for Multiple Columns. Need to Modify the Code

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    dammam
    MS-Off Ver
    Excel 2013
    Posts
    31

    Talking Timestamps for Multiple Columns. Need to Modify the Code

    I have attached the excel I'm working on.
    Vendor Tracking.xlsm

    there are 10 columns that I need a timestamp when I enter data in the cell next to them.

    I have a code that give me perfect results if used for maximum of only two columns, if I enter more columns in the code, it give me an error.

    Please see the attached sheet, first sheet is with the working sheet only two cells works. second sheet with

    I'm noob in VBA, I copied this code from a different website.

    Please help!
    Last edited by bssol; 08-17-2013 at 04:48 AM. Reason: Wrong Attachement

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Hi,

    Sorry to be a pain, but I'm not at my computer at the moment but I would really like to help you. Can you please copy the code that writes the timestamps (the entire subroutine please) and paste it here so I can examine it while I am away from my computer.

    Thanks

  3. #3
    Registered User
    Join Date
    01-11-2013
    Location
    dammam
    MS-Off Ver
    Excel 2013
    Posts
    31

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Yes Sure

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("E6001:E10000", "M6001:M10000"), .Cells) Is Nothing Then
    Application.EnableEvents = False
    With .Offset(0, 1)
    .NumberFormat = "dd/mmm/yyyy"
    .Value = Date
    End With
    Application.EnableEvents = True
    End If
    End With
    End Sub


    It turned out the problem is bigger than I thought. it works in thw whole range between the two columns which is not what I want. I want only cells in E6001:E10000" and "M6001:M10000", in addition to other columns, but not between them.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Hi, bssol,

    maybe try
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    01-11-2013
    Location
    dammam
    MS-Off Ver
    Excel 2013
    Posts
    31

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    WOOOOW

    It Actually works!

    Thank you HaHoBe. You have solved my problem!

    You are my champion

  6. #6
    Registered User
    Join Date
    12-06-2017
    Location
    MO, USA
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    1

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    I am creating a work sheet in Excel where I want to track Start and End time of a particular transaction using a timestamp. A column will also show the time duration. Please see picture below. Can someone help me?


    excel issue.PNG


    I found 2 VBA Codes that records time stamps. However, I don't know how to merge them so they will work in this sheet:


    Code 1 . It shows timestamp in "Start Time" column (B4) when you enter a transaction number in "Case ID" coumn (A4).


    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Target.Column = 1 And Target.Value <> "" Then
    Application.EnableEvents = False
    Target.Offset(0, 1) = Format(Now(), "mm/dd/yyyy HH:mm:ss")
    Application.EnableEvents = True
    End If
    Handler:
    End Sub


    Code 2. It shows time stamp in "End Time" column (D4) if a selection from a dropdown in "Status" column (C4) is selected.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    xCellColumn = 3
    xTimeColumn = 4
    xRow = Target.Row
    xCol = Target.Column
    If Target.Text <> "" Then
    If xCol = xCellColumn Then
    Cells(xRow, xTimeColumn) = Now()
    End If
    End If
    End Sub

  7. #7
    Registered User
    Join Date
    11-17-2018
    Location
    Bangalore
    MS-Off Ver
    2013
    Posts
    1

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Hi,

    Can u pls give me the suggestion to put the time along with date.

    .NumberFormat = "dd/mmm/yyyy"

    Regards,
    Manikandan

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Quote Originally Posted by kmani_chennai View Post
    Hi,

    Can u pls give me the suggestion to put the time along with date.

    .NumberFormat = "dd/mmm/yyyy"

    Regards,
    Manikandan
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    02-02-2018
    Location
    RIYADH
    MS-Off Ver
    2016
    Posts
    1

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Good day, Sir.

    I'd want to ask if you could add a statement to the code that says "if the cell is BLANK (where you need to enter a value), then the cell where the date was entered shall be removed or should be blank."

    Thank you in advance.


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("E6001:E10000, M6001:M10000, O6001:O10000, Q6001:Q10000, S6001:S10000, U6001:U10000, W6001:W10000, Y6001:Y10000, AA6001:AA10000, AC6001:AC10000, AE6001:AE10000"), Target) Is Nothing Then
    Application.EnableEvents = False
    With .Offset(0, 1)
    .NumberFormat = "dd/mmm/yyyy"
    .Value = Date
    End With
    Application.EnableEvents = True
    End If
    End With
    End Sub
    Last edited by danedward_anore; 02-11-2022 at 02:29 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,330

    Re: Timestamps for Multiple Columns. Need to Modify the Code

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need to modify my formula for organizing data with timestamps
    By n_ant in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2013, 01:54 PM
  2. [SOLVED] Import multiple CVSs to one worksheet - Modify VBA code
    By EIKA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2013, 05:57 PM
  3. Modify Code to Run across multiple worksheets in directory
    By tdm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2012, 03:29 PM
  4. Replies: 7
    Last Post: 11-08-2012, 09:46 PM
  5. Modify code to perform on multiple worksheets
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2011, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1