Hyperlinks worked . . . until they didn't

[Sheryl Lucas]

I maintain several databases of client inquiries in an Excel spreadsheet. The spreadsheets include links to pdf files of the actual inquiries and the responses. The linked files are located on the same drive as the worksheets.

I have just completed a massive relinking project, as we were changing where the files were stored, and everything was working fine. All of a sudden, I can no longer edit links. If I click on a blank cell, the hyperlink option is grayed out; if I click on a cell containing a link, the edit and open options are grayed out, and the only option available to me is to remove the hyperlink. If I remove it, I'm unable to link it again.

Protection is off and the file is not shared. I did find out that a coworker opened a read-only copy of one of the files while I was in it earlier. But even though she closed the file, I am still having the issue. I'm also having the problem in a file she did not have open at all.

I'm using Excel 2013, and both files are in Compatibility Mode. I turned off the compatibility mode and saved the file as an xlsx, and that made no difference.

If I start a brand new blank file, everything is fine.

 

[pbornemeier]

If you copy a page containing the dead hyperlinks to a new workbook, do they work there?

Does this code "see" all of your hyperlinks:

Sub Inventory_Hyperlinks()

    Dim wks As Worksheet
    Dim hyp As Hyperlink
    Dim lWksHyperlinkCount As Long
    Dim sOutput As String
    
    For Each wks In ThisWorkbook.Worksheets
        lWksHyperlinkCount = 0
        For Each hyp In wks.Hyperlinks
            lWksHyperlinkCount = lWksHyperlinkCount + 1
        Next
        If lWksHyperlinkCount > 0 Then sOutput = sOutput & wks.Name & "  (" & lWksHyperlinkCount & ")"
    Next
    If Len(sOutput) > 0 Then MsgBox sOutput, , "Hyperlink Report"
End Sub

If so try to renew them using VBA. Note that this does not "hurt" the hyperlinks in my test workbook, but since mine aren't broken, I can't guarantee it will fix yours

Sub RenewHyperlinks()

    Dim wks As Worksheet
    Dim hyp As Hyperlink
    Dim sOutput As String
    
    For Each wks In ThisWorkbook.Worksheets
        For Each hyp In wks.Hyperlinks
            hyp.Address = hyp.Address
            hyp.TextToDisplay = hyp.TextToDisplay
            hyp.ScreenTip = hyp.ScreenTip
        Next
    Next
    
End Sub

 

[Sheryl Lucas]

Thanks for your response, Phil. I copied the page into a new workbook like you suggested and my hyperlinks worked again. As I was making edits to the worksheet, however, the problem recurred, but if I would click outside the cell and then back in it, it worked fine. Eventually it dawned on me what was happening.

I had been using the find function to locate cells with hyperlinks that I missed when I made the changes. So I would highlight the columns with the links, run the find, and when I got to one, I tried to edit the hyperlink. But I still had the two entire columns highlighted from the find operation. Once I clicked outside the selected columns and went back into the individual cell, everything was fine. At least everything seems fine. Hopefully it stays that way!