+ Reply to Thread
Results 1 to 22 of 22

How to enter multiple timestamps in a single sheet?

  1. #1
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    How to enter multiple timestamps in a single sheet?

    Hi All,

    In the attached excel file, below VBA code is entered for timestamp.

    Please Login or Register  to view this content.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRInt As Integer
    Dim xDStr As String
    Dim xFStr As String
    On Error Resume Next
    xDStr = "A" 'Data Column
    xFStr = "B" 'Timstamp Column
    If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then
    xRInt = Target.Row
    Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss AM/PM")
    End If
    End Sub
    Please Login or Register  to view this content.


    Column B enters timestamp based on entries in Column A. Similarly, i want timestamp to be entered in column D only if the state in Column C is Closed Complete, Closed Incomplete or Closed Skipped.


    Also, the timestamp should be applicable only to the column range starting from B2 and D2 respectively because the first row contains headings.

    Can someone please help me with the code?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    Not sure how you really want to happen.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Thank you for your response Jindon.

    What you have understood is correct.

    However, the code isn't working well. Timestamp is being noted only for initial entry (in any cell) after opening the file. Later when i try to change the value in Column A and C, the timestamps remain same in Column B and D and are not being updated.

    I have to close excel and restart for it to work. Still, only the first entry (in any cell) timestamp is noted and not the others.

    Can you look into it once?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    OOps.. of course.
    Please Login or Register  to view this content.
    Needs to be
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Hi Jindon,

    I applied the revised code in the sheet and it appeared to work fine. However, after 5 minutes, no timestamp is being recorded.

    Can you check the attached excel file once if I made any mistake in the code?

    Also, the timestamp remained in columns B and D even if i deleted the contents in cells of Column A and C. Is there something you can do such that if there are no values in Column A and C, then the Columns B and D should also be blank and no timestamp should be entered.

    Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    If you delete the contents in col.C, it should clear the time stamp if there is.

    If you also want to clear Col.B when you delete the contents in Col.A then add one line in bold
    Please Login or Register  to view this content.
    However, after 5 minutes, no timestamp is being recorded.
    Can not replicate this issue.

  7. #7
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Yeah its working now .

    2 questions Jindon.

    1. Along with row 1, how can i exclude rows 2 &3?

    2. Can i use the same code in another sheet of the same workbook?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    If you want to use the same code to multiple worksheets then use ThisWorkbook_SheetChange event.

    Delete current code in Sheet1.
    To ThisWorkbook code module
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Ok Jindon, but what if the columns are different in both the sheets.

    For example, in the second sheet of the attached file, timestamp is to captured in columns E, O, S and X.

    However, timestamp in column S is to be captured only if Status in column R is "Completed"

    Similarly, timestamp in column X is to be captured only if Status in column W is "Closed".

    Note: Timestamp for columns in sheet 1 (Tickets) to remain same i.e. column B, D and F.

    Many thanks in advance.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Also to inform you, I already have below 2 codes in ThisWorkbook sheet of my excel file.

    Please Login or Register  to view this content.
    Private Sub Workbook_Open()
    Worksheets("Tickets").Activate
    End Sub
    Please Login or Register  to view this content.



    Please Login or Register  to view this content.
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim wsName As String, r As Range
    If Target.Column <> 2 Then Exit Sub '<---
    Select Case Sh.Name
    Case "Tickets", "DM Records"
    Cancel = True
    wsName = IIf(Sh.Name = "Tickets", "DM Records", "Tickets")
    Set r = Sheets(wsName).Columns(2).Find(Target, , , 1) '<---
    If Not r Is Nothing Then Application.Goto r
    End Select
    End Sub
    Please Login or Register  to view this content.




    Can i use the new code below these codes?

    Sorry for the trouble Jindon. Many thanks for your assistance.
    Last edited by rizwanulhasan; 01-14-2022 at 12:59 PM.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    Now you have working code for Sheet1, not ThisWorkbook code module.
    Why don't you try that for yourself?
    Just adjusting target column & output collumn refernce.

  12. #12
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    In the attached file I tried the code provided by you for multiple worksheets To ThisWorkbook code module and modified the sheet names.

    However, for both the sheets its selecting the same columns for timestamp.

    May i know where exactly shall i enter the timestamp target column (E,O,S,X) for the second sheet.

    ( timestamp in column S is to be captured only if Status in column R is "Completed" and timestamp in column X is to be captured only if Status in column W is "Closed" )

    Also as mentioned earlier, i already have two codes in ThisWorkbook. Can i paste this one below those two codes?

    Sorry i only know the basics of excel and am not at all aware of VBA codes and therefore unable to try most of the things myself.

    Your assistance is highly appreciated. Thank you.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    No benefit of using ThisWorkbook event code.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Working with absolute perfection. Thank you so much Jindon.


    A few queries:


    If the sheet is locked, then i get the below error while capturing timestamp:

    Run-time error '1004'

    Any solution for the codes to run on a protected cell and protected sheet?

    (Timestamp cells are locked and data entry cells are unlocked when the sheet is protected)



    Also since the time i entered these two codes on the two sheets, there's a lag of 1-2 seconds in displaying the timestamp. Is excel getting slow because i have entered codes in both the sheets?



    Also, if any error occurs, i have to close and restart excel for the codes to work again. Is this normal?


    Thanks again for your great help.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    Why are you asking one by one?

    Upload your workbook that you are working with, otherwise I need to GUESS too much.

  16. #16
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    As requested, PFA.



    Issues:


    If the sheet is locked, then i get the below error while capturing timestamp:

    Run-time error '1004'


    Any solution for the codes to run on a protected cell and protected sheet?

    (Cells are already locked and unlocked as per the requirement)



    If i press delete in a cell in column whose timestamp is noted, then i usually get this error:

    Run-time error '13'

    Type mismatch






    Since the file contains multiple VBAs, can this be the reason for minor lag?
    Last edited by rizwanulhasan; 01-15-2022 at 07:04 AM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    There is no worksheet protected...

  18. #18
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Sorry.

    PFA.

    The timestamp VBAs are for first 2 sheets and both he sheets will always be locked.

    No passwords as of now.
    Last edited by rizwanulhasan; 01-15-2022 at 09:00 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    1) Tickets sheet
    Replace the code with
    Please Login or Register  to view this content.
    2) DM Records sheet
    Please Login or Register  to view this content.
    The reason why the sheet is slow is that formula takes time to calculate as well as conditional formattings...

    If you use vba, everything should be done by code.

  20. #20
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    Copied as instructed. Working great.

    Just 1 error which wasn't encountered earlier, but i am facing now.

    When i am trying to extend my table (by selecting row no. 503 from column A to V) and dragging down using the small + button, i am getting below error and VBA stops working:


    Run-time error '13'

    Type mismatch



    Any solution to this?
    Last edited by rizwanulhasan; 01-15-2022 at 09:49 AM.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,374

    Re: How to enter multiple timestamps in a single sheet?

    Insert bold line in each procedure
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    73

    Re: How to enter multiple timestamps in a single sheet?

    I wonder why sheets are locking automatically after few actions such as copy paste etc.

    Rest all going perfect. You are a true troubleshooter Jindon.

    Thanks a ton.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Macros single event (Enter & Exit) for multiple userform textboxes
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2019, 12:54 PM
  2. autocomplete/formula to enter multiple values (words) in single cell.
    By ap556 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-07-2015, 05:28 AM
  3. posting day book details and how to enter multiple formulas in a single cell
    By hereischandu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2014, 04:10 AM
  4. [SOLVED] Pull data if there is any from a single column in multiple worksheets into a single sheet
    By bcas77 in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 07-11-2013, 01:22 PM
  5. Replies: 5
    Last Post: 12-06-2010, 04:39 PM
  6. Multiple Timestamps
    By Push Latency in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2007, 05:57 PM
  7. Replies: 2
    Last Post: 06-09-2006, 05:40 PM

Tags for this Thread

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