Insert timestamp when hyperlink is clicked

[annakirsten]

I would like to know if there is a way to automatically insert a timestamp into a cell when a hyperlink is clicked. I have a document with hyperlinks in column G. I want to populate the cell to the right in column H with the timestamp for when the hyperlink is clicked. So if the hyperlink in G2 is clicked, I want a timestamp for the time when it was clicked to show in H2, in the format MM/DD/YYYY HH:MM AM/PM.

In any row, if there is already a timestamp in column H, and the link in column G is clicked again, I do not want the cell data to change. So if H2 already has a timestamp of 6/13/2017 12:20 PM, and the hyperlink in G2 is clicked again, I do not want the timestamp to update.

Thank you in advance for your help.

 

[cerfani]

https://msdn.microsoft.com/en-us/library/office/ff838843.aspx

[COLOR=#000000][FONT=Consolas]Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)[/FONT][/COLOR]
    Dim dateRange As Range
    Set dateRange = Target.Range.Offset(0, 1)
    If IsEmpty(dateRange) Then dateRange.Value = Now
[COLOR=#000000][FONT=Consolas]End Sub[/FONT][/COLOR]

test that

 

[Jeffrey Mahoney]

What I found was that if you have more than one hyperlink in the same sheet that is identical to another, TARGET returns more than one cell. This code tests if your hyperlink is in the range you want. You can alter the Range selection for column G to your liking. The code in red below uses activecell to compare the Column G values. When you click a hyperlink, it selects the cell it resides.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  Dim i As Range
  Dim TS As Range
  
  [COLOR=#ff0000]Set i = Intersect(ActiveCell, Range("G2:G1000"))[/COLOR]
  If Not i Is Nothing Then
    Set TS = i.Offset(0, 1)
    If TS.Value = "" Then
      TS.Value = Now()
    End If
  End If
  
    
End Sub

 

[annakirsten]

Thank you both for responding. Unfortunately, I wasn't able to make either suggestion work.

I right-clicked on the sheet tab at the bottom, selected "View Code", then clicked VBAProject > Microsoft Excel Objects > Sheet1 in the folder tree in the left pane. I placed each bit of suggested code into the window separately, clicked save, and then tested it in the main Excel window. I'm not getting an error, but nothing is showing up in the H column.

I may be doing something wrong, but I was able to get another unrelated function by using the steps described above. I also tried removing that code temporarily to see if it was affecting the link-timestamp code, but the result didn't change.

Again, your help is appreciated. Let me know if I can provide any other information.

 

[Jeffrey Mahoney]

Your hyperlinks are on Sheet1?
Your hyperlinks are in column G?
You clicked on a hyperlink in column G and nothing happened?
Did the hyperlink work, did it actually take you to a folder or open a file?
You weren't trying to run both sets of code at the same time were you?

I have to ask silly questions. It works on my end, so I'm a little puzzled.

 

[annakirsten]

Not silly questions, I totally understand. I am not sure why it isn't working either.

Yes, links are on Sheet1 in column G. I click on the hyperlink in G22, for example, and it shows the MS Office security pop-up warning that some files are dangerous and do I want to open the file? After clicking OK, it does open the correct file. No timestamp shows in column H - cell H22 remains empty.

I tried each set of code separately. Right now, this is all I have in there:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim i As Range
Dim TS As Range

Set i = Intersect(ActiveCell, Range("G2:G1000"))
If Not i Is Nothing Then
Set TS = i.Offset(0, 1)
If TS.Value = "" Then
TS.Value = Now()
End If
End If
End Sub

 

[Jeffrey Mahoney]

Do you know how to Debug Code? Do you have macros enabled in Excel? It almost sounds like you're not getting an event when you click on the hyperlink.

Just for giggles, go into the code for Sheet1. Put your cursor on the line that starts "Set i = ...". Press F9. This is a toggle to Toggle a Breakpoint. If you click on one of those hyperlinks, you should be brought to that line of code with it highlighted yellow. It then waits for you to step through the code. F8 is step one line. If that doesn't happen, you are not getting the event triggered.

Run this:

Sub Fixit()
  Application.EnableEvents = True
  
End Sub

If that doesn't fix it then you may not have macros enabled.

Jeff