VBA Hyperlinks.Add creates working hyperlink but text display is not visible in Excel 2010

Question

I have a basic Hyperlinks.Add call developed in VBA Excel 2007 (Windows 7) and it works all fine. However, a user with Excel 2010 runs the same program and while the hyperlink is written out, it works (hovering over the cell with the link shows the URL) but the text display is a no show. The cell is blank.

Here's the code.

Private Sub Write_PartSpecPartDetails(ByRef p_wksCompDetailReport As Worksheet, p_lngRowNow As Long, p_strReturnedRecords() As String, p_typCDCols As CompDetailCols)

With p_wksCompDetailReport

    .Range(p_typCDCols.strPartsSpecCol & p_lngRowNow).Value = p_strReturnedRecords(XML_ARRAY_PART_ELE)
    .Range(p_typCDCols.strPartsSpecStatusCol & p_lngRowNow).Value = p_strReturnedRecords(XML_ARRAY_PS_STAT_ELE)
    .Range(p_typCDCols.strOracleStatus & p_lngRowNow).Value = p_strReturnedRecords(XML_ARRAY_ORACLE_STAT_ELE)
    .Hyperlinks.ADD Anchor:=.Range(p_typCDCols.strWebLinkCol & p_lngRowNow), _
                        Address:=ITB_WEBPN_URL & p_strReturnedRecords(XML_ARRAY_PART_ELE) & "?OpenDocument", _
                        TextToDisplay:=ITB_WEBPN_PREFIX & p_strReturnedRecords(XML_ARRAY_PART_ELE)

End With

End Sub

The user's file is saved as Excel 2003 (.xls), it's not shared but it is protected. Thoughts?

This ? "A similar result can be had by applying a custom format to the cell. Just use the format ";;;" (that's three semicolons, without the quote marks) and the information in the cell disappears from view. Again, you can still click the link, even though it is quite invisible." Would be kind of random for the cells to have that format by chance though... — Tim Williams

CarloC CarloC · Accepted Answer · 2015-09-03T21:00:15

The problem is the protected worksheet. While a protected Excel 2007 worksheet allows for formatting and adding hyperlink text display without implicitly setting it so, in Excel 2010 it appears you must do so. For my purposes, I simply unprotected the sheet, let the program do its thing, then reset the Protect at the end. So as a precursor to the actual hyperlinking routine, I added a clean up routine.

                ' Clear links and colour formatting from the WEBLink col etc:  _Buggsie.178
            With p_wksCompDetailReport
                .Unprotect Password:=PROTECT_PASSWORD
                If .Hyperlinks.Count > 0 Then
                    .Hyperlinks.Delete
                End If
                With .Range(p_typCDCols.strWebLinkCol & REPORT_DATA_ROW_START & ":" & p_typCDCols.strWebLinkCol & p_lngRowLast)
                    .Interior.ColorIndex = xlNone
                    .Cells.Clear
                End With
            End With

VBA Hyperlinks.Add creates working hyperlink but text display is not visible in Excel 2010

1 Answers