Insert current time

[AccessPro]

I know I did this before. But can't find where.
It was VBA code that inserted the current time (24hr) when the user entered a "t" in a cell.

TIA

Bob

 

[Joe4]

Which cell/cells that you want this to trigger off of (cell location)?
And where do you want the timestamp to appear, relative to where the "t" is being entered (in the next cell over to the right, or somewhere else)?

You would use an Event Procedure, which is VBA code that is automatically run when a certain event happens, like the updating of a particular cell.
Here is code that will run automatically upon a "t" being entered in column B.
Note that this must be placed in the proper Sheet module in VBA (the sheet you want it to run on).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B:B"))
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell = "t" Then
            cell.Offset(0, 1) = Now()
            cell.Offset(0, 1).NumberFormat = "h:mm:ss;@"
        End If
    Next cell

End Sub

 

[AccessPro]

Thanks for the response. I want to run this procedure on any cell in a certain column.
l want the same cell that I enter the "t" to then reflect the current time.

thx

bob

Which cell/cells that you want this to trigger off of (cell location)?
And where do you want the timestamp to appear, relative to where the "t" is being entered (in the next cell over to the right, or somewhere else)?

You would use an Event Procedure, which is VBA code that is automatically run when a certain event happens, like the updating of a particular cell.
Here is code that will run automatically upon a "t" being entered in column B.
Note that this must be placed in the proper Sheet module in VBA (the sheet you want it to run on).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B:B"))
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell = "t" Then
            cell.Offset(0, 1) = Now()
            cell.Offset(0, 1).NumberFormat = "h:mm:ss;@"
        End If
    Next cell

End Sub

 

[Joe4]

Thanks for the response. I want to run this procedure on any cell in a certain column.

That is what my code will do. It is written for column B, but you can change that to whatever column you want.

l want the same cell that I enter the "t" to then reflect the current time.

Do you want it to replace the "t", or show both like "t 17:02:00"?

 

[AccessPro]

Great.
Yes I want to replace the "t" with the time.

BUT
Maybe I should create a new thread. This project will use Excel on Android. I do mostly Access programming.
It appears that "macros" and VBA will not run on the droid app for Excel ??

thanks again,
bob

 

[Joe4]

Yes I want to replace the "t" with the time.

Then you would need to do is remove the Offset part, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B:B"))
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell = "t" Then
            cell = Now()
            cell.NumberFormat = "h:mm:ss;@"
        End If
    Next cell

End Sub

Maybe I should create a new thread. This project will use Excel on Android. I do mostly Access programming.
It appears that "macros" and VBA will not run on the droid app for Excel ??

I believe that is correct, you cannot use VBA on the Excel Android app.
I am afraid that you cannot do what you want without VBA, so there isn't any point in starting a new topic on it (unless you want to take a totally different approach).

 

[AccessPro]

Thanks again Joe.
I guess I will have to bail to Sheets.

bob

I believe that is correct, you cannot use VBA on the Excel Android app.
I am afraid that you cannot do what you want without VBA, so there isn't any point in starting a new topic on it (unless you want to take a totally different approach).