Excel macro

[bchadwick]

Hey Guys,
Don't know if this is possible, but I will ask. I have a spreadsheet with hyperlinks in it and want to have the current date inserted into column B cell when cell in column A is clicked. Suggestions?

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you click on any Hyperlink in column A todays date will be entered in column B

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified 5/24/18 5:15 AM EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Hyperlinks.Count > 0 Then Target.Offset(, 1).Value = Date
End If
End Sub

 

[JackDanIce]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Hyperlinks.Count + Target.Cells.Count + Target.Column = 3 Then Target.Offset(, 1).Value = Date
    
End Sub

 

[Peter_SSs]

Some comments/suggestions.

@JackDanIce
Whilst your code does put the date in col B when a hyperlink** in col A is clicked, it will also insert, incorrectly, dates in other circumstances. Examples:
- Select any single cell (not containing a hyperlink) in column B (here your count is 0+1+2=3 so date is entered in column C)
- Select any 2 cells (not containing hyperlinks) in column A (here your count is 0+2+1=3 so dates will be entered in two cells in column B though no hyperlink is clicked)

@bchadwick
** There are two types of hyperlinks that can be used in excel. One is set up by a formula and one through Insert -> Link
Both the codes suggested above only work with the Insert -> Link type of hyperlink.
The same applies to my code below. I am offering the alternative code since generally in a worksheet a lot of 'selecting' may take place. If it turns out that much of the 'selecting' is not related to clicking hyperlinks then any Worksheet_SelectionChange code may be triggered much of the time when not necessary. Most likely that will not be any issue for you, but the following code will only get triggered when a hyperlink is clicked. Anyway, it could be an option for you & it is a very simple code.
This code would also go in the worksheet's module - same place as described by MAIT for his code. However, don't have my code and a Worksheet_Change code in the workbook at the same time trying to do the same job - just one at a time.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  If Target.Range.Column = 1 Then Target.Range.Offset(, 1).Value = Date
End Sub

Further, if you don't have hyperlinks anywhere in the sheet except column A, then the code could be even simpler.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  Target.Range.Offset(, 1).Value = Date
End Sub

 

[JackDanIce]

@Peter_SSs thanks for the spot, trying to be too efficient in what evaluates to TRUE for the IF statement!

Maybe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With Target
        If (.Hyperlinks.Count + .Cells.Count + .Column) = 3 And (.Hyperlinks.Count * .Cells.Count * .Column) = 1 Then Target.Offset(, 1).Value = Date
           'Alternatively:    If (.Hyperlinks.Count + .Cells.Count + .Column) * (.Hyperlinks.Count * .Cells.Count * .Column) = 3 Then Target.Offset(, 1).Value = Date
    End With
End Sub

However, if only found in Column A, like the shortness of

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Target.Range.Offset(, 1).Value = Date
End Sub

 

[bchadwick]

Thanks Guys. A little clarification and some questions: First I misspoke on the columns, hyperlink are in column B, and dates are in column C. All of the hyperlinks are links, this sheet is an index of Word docs. In Excel 2013 the first code above works fine, the other two do not. Also, none of the code above works in Mac 2016, which is supposed to be close to Windows versions. Suggestions?

 

[My Aswer Is This]

Are you saying the code in Post 2 works?

Not sure what this means:

the first code above works fine.

It's always important to point out when your using a Mac.

I have no Mac so no way to test this.
​

 

[bchadwick]

The first posted code works. After redoing the links on the Mac, it works. I work in both Windows and Mac-

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

The first posted code works. After redoing the links on the Mac, it works. I work in both Windows and Mac-

 

[bchadwick]

One more question on this workbook-is it possible to have the macro insert the number of document pages of the link in an other column? If it's a big deal, no worries.