An issue of time

[KTunstall]

Long time lurker, first time poster.

I have looked on this forum (and others) for an answer to no avail.

I have a workbook that logs time(s) through user interaction (either CTRL+SHFT+Colon or a double click on a cell).
The times are logging events (in my case, objections and the inclusion of exhibits).
The log entries are non-contiguous as well (multi-column and hundreds of rows).
Whenever a user changes a cell on the Log, I want the time to fire on the hidden time tab.

To simplify (and bifurcate) the example, let's assume I have two tabs in the workbook.
The first tab is called "Log" and the second tab is called "time"

In say A2-A10, A12-A20, A22-30, etc., etc., the user will place a numeric value (exhibit number or letter).
When they perform this task, I want the following to happen on the "time" tab.

Again in A2-A10, A12-A20, A22-A30, etc., etc., I need the exhibit number (or letter) to be noted. (this is easy).
However, upon that instance being triggered, in the next column I need the time to fire.
I will need to further utilize those times in other calculations and for reporting purposes.

I am not afraid of VBA, and I do not want to use a circular formula.
Looking for suggestions.

 

[MARK858]

Lets get this clear, is the below what you mean....

1. The user enters a number or letter in a cell in one of the ranges that you mentioned (from now on I am going to use A3 for this) on the log sheet.
2. On the Time sheet the same entry is to appear in cell A3
3. On the Time sheet a Time stamp is to appear in cell B3

If the above is correct should the Time stamp be just time or date and time?

 

[KTunstall]

Absolutely correct. You are spot on.

 

[KTunstall]

Time only in an hh:mm:ss format.

 

[KTunstall]

I used a simple formula to bring over the exhibit info

=IF('Log'!A3<>"",'Log'!A3,"")

That part is easy.

I can't use Now() as those values will change every time the workbook is opened.
Looking for a simple yet elegant solution. Heh.

 

[MARK858]

Don't worry about the formula we'll bring it all over in the macro.

 

[KTunstall]

Sounds good.

 

[MARK858]

Try putting the code below in the worksheet module of the "Log" sheet (Right click the sheet tab,click view code and paste in the window that appears) and test.
Format column B in the Time sheet how you wish.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2:A10, A12:A20, A22:A30")) Is Nothing And Target.Cells.Count = 1 Then
    
    Application.EnableEvents = False
        Sheets("Time").Cells(Target.Row, Target.Column) = Target.Value
        Sheets("Time").Cells(Target.Row, Target.Column + 1) = Time
    Application.EnableEvents = True
    
    End If
    
End Sub

 

[KTunstall]

Didn't seem to work.
I will clarify further that my actual entry location for the exhibit identifier is on A167-A191 and G167-G191.
The location for the entry on my "time" tab is A418 and C418.
I'll address the second column after the first column works.
Should have been more clear about multi-column.

I have attached an image of each.

So

Application.EnableEvents = False
        Sheets("Times2").Cells(Target.Row + 251, Target.Column) = Target.Value
        Sheets("Time2").Cells(Target.Row + 251, Target.Column + 1) = Time
    Application.EnableEvents = True

Doesn't seem to work (with reference to the first row at least.

 

[MARK858]

It works for me with the ranges that you quoted in your first post did that not work? If you haven't tested it with your original ranges please test with the file in the link below please (your ranges are highlighted yellow).

KTunstall.xlsb | Powered by Box

app.box.com

my actual entry location for the exhibit identifier is on A167-A191 and G167-G191.
The location for the entry on my "time" tab is A418 and C418.

The above does not match with the questions I asked in post #2 that you stated were all correct or the ranges you quoted in post number 1. .