Cannot use hyperlink when protecting sheets without allowing user to select locked cells

Question

I have a worksheet that has a bunch of dynamic hyperlinks that change based on a drop down menu. Only the cells with drop down menus are unlocked. I have "select locked cells" unchecked so that when I protect the sheet, users can only select the drop down menus. Unfortunately, when I do this, the hyperlinks are no longer usable.

Does anyone know how to work around this?

UPDATE*

as requested, the code for my dynamic hyperlink cells:

=IF(ISNA(MATCH(B4,'Data Sheet'!A2:A103,0)),"",HYPERLINK(VLOOKUP(B4,'Data Sheet'!A:S,7,FALSE),VLOOKUP(B4,'Data Sheet'!A:S,5,FALSE)&" - "&VLOOKUP(B4,'Data Sheet'!A:S,6,FALSE)))

1) Cell B4 is the drop down where the user selects a particular option. The hyperlinks change based on this selection.

2) 'data sheet' is a separate sheet that houses all of the reference data in an array.

this basically says: does the value in B4 match the first column in my data chart? if so, use a hyperlink formula using VLOOKUP to insert the corresponding URL into the formula.

Answer 1

This is my understanding of the settings and requirements:

Settings

• There is a protected worksheet with a dropdown menu which updates other cells containing VLOOKUP\HYPERLINK formulas.

• All cells in the worksheet, excluding the dropdown menus, are protected.

• The value of the cells containing VLOOKUP\HYPERLINK formulas, could equal to a www address or blank depending on the value of the dropdown menu. As such, all hyperlinks point to web pages or are blank.

• The worksheet EnableSelection is set to xlUnlockedCells which determines that once the worksheet is protected “Only unlocked cells can be selected.”

Requirements - Need to maintain the worksheet protected to safeguard al the contents including the VLOOKUP\HYPERLINK formulas.

• Need to allow users to select\activate only unprotected cells mostly for aesthetic reasons and to deliver a professional product.

This solution uses the following resources

• The HYPERLINK function
• An UDF (user defined function)
• Two Public Variables and
• The Worksheet_BeforeDoubleClick event

When an UDF is wrapped into a HYPERLINK function it causes that every time the mouse hovers over the cell containing the combined formula of HYPERLINK(UDF,[FriendlyName]) the UDF is triggered.

We’ll use a Public Variable to hold the LinkLocation, to be used later to follow the hyperlink upon users decision.

And a second Public Variable to set the time when the LinkLocation was last updated.

We’ll mimic the manner in which the hyperlink is “normally” activated:

• by which an user selects a cell and clicks the hyperlink in the selected cell.

• Instead the user hovers over the cell with the hyperlink (the UDF feeds the LinkLocation and the time into the public variables) and DoubleClicks the cell (triggering the worksheet event to follow the hyperlink, validating first the time when the LinkLocation was last updated to ensure it stills actual and clearing the LinkLocation variable).

First we need to ensure that the formulas used in the worksheet to generate the Dynamic Hyperlinks have the appropriated structure:

Assuming the current VLOOKUP\HYPERLINK formulas have the following structure: (have to work based on assumptions as the actual formula was not provided)

=IFERROR( HYPERLINK( VLOOKUP( DropDownCell , Range , Column, False ), FriendlyName ), "" )

We need to change that formula to the following structure:

=IFERROR( HYPERLINK( UDF( VLOOKUP( DropDownCell , Range , Column, False ) ), FriendlyName ), "" )

The following procedures take care of modifying the formulas structure to make them suitable for the solution proposed. Suggest to copy both in a separated module named “Maintenance”.

Option Explicit

Private Sub Wsh_FmlHyperlinks_Reset()
Const kWshPss As String = "WshPssWrd"
Const kHypLnk As String = "HYPERLINK("
Dim WshTrg As Worksheet, rHyplnk As Range
Dim rCll As Range, sHypLnkFml As String
Dim sOld As String, sNew As String

    Rem Application Settings
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Rem Set & Unprotect Worksheet
    Set WshTrg = ActiveSheet
    WshTrg.Unprotect kWshPss

    Rem Find Hyperlink Formulas
    If Not (Rng_Find_Set(WshTrg.UsedRange, _
        rHyplnk, kHypLnk, xlFormulas, xlPart)) Then Exit Sub
    If rHyplnk Is Nothing Then Exit Sub

    Rem Add Hyperlinks Names
    For Each rCll In rHyplnk.Cells
        With rCll
            sHypLnkFml = .Formula
            sOld = "HYPERLINK( VLOOKUP("
            sNew = "HYPERLINK( Udf_HypLnkLct_Set( VLOOKUP("
                sHypLnkFml = Replace(sHypLnkFml, sOld, sNew)
            sOld = ", FALSE ),"
            sNew = ", FALSE ) ),"
                sHypLnkFml = Replace(sHypLnkFml, sOld, sNew)
            .Formula = sHypLnkFml
    End With: Next

    Rem Protect Worksheet
    WshTrg.EnableSelection = xlUnlockedCells
    WshTrg.Protect Password:=kWshPss

    Rem Application Settings
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub


Function Rng_Find_Set(rInp As Range, rOut As Range, _
    vWhat As Variant, eLookIn As XlFindLookIn, eLookAt As XlLookAt) As Boolean
Dim rFound As Range, sFound1st As String
    With rInp
        Set rFound = .Find( _
            What:=vWhat, After:=.Cells(1), _
            LookIn:=eLookIn, LookAt:=eLookAt, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not (rFound Is Nothing) Then
            sFound1st = rFound.Address
            Do
                If rOut Is Nothing Then
                    Set rOut = rFound
                Else
                    Set rOut = Union(rOut, rFound)
                End If
                Set rFound = .FindNext(rFound)
            Loop While rFound.Address <> sFound1st
    End If:  End With
    Rem Set Results
    If Not (rOut Is Nothing) Then Rng_Find_Set = True
End Function

These are the Public Variables and the UDF. Suggest to copy them in a separated Module.

Option Explicit

Public psHypLnkLoct As String, pdTmeNow As Date

Public Function Udf_HypLnkLct_Set(sHypLnkFml As String) As String
    psHypLnkLoct = sHypLnkFml
    pdTmeNow = Now
End Function

And copy this procedure in the Module of the protected worksheet with the dynamically generated hyperlinks.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Now = pdTmeNow And psHypLnkLoct <> Empty Then
        ThisWorkbook.FollowHyperlink Address:=psHypLnkLoct, NewWindow:=True
    End If
End Sub

Answer 2

If you are happy to use VBA, you could use the following code for the Sheet(s) in question, this will replicate the click event for the hyperlink and try and open in the target's native format

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If InStr(1, Target.Formula, "HYPERLINK", vbTextCompare) > 0 Then

    On Error Resume Next

    Target.Hyperlinks(1).Follow (True)

    On Error GoTo 0

End If

End Sub

Update

I think I have a bit of a work around. I've pinched some code from here which allows a roll over action to trigger some vba. So, let's say you have your link in cell A1. Change your link to the following:

=IFERROR(HYPERLINK(MyMouseOverEvent("http://www.google.com"),"Hover"),"Hover")

You can change your link dynamically providing it returns a string. Now create a new module and paste in the following:

Public Function MyMouseOverEvent(varLink As String)
    varResponse = MsgBox("Would you like to open link to: '" & varLink & "'?", vbYesNo, "Confirm")
    If varResponse = vbYes Then
        ActiveWorkbook.FollowHyperlink Address:=varLink, NewWindow:=True
    End If
End Function

The only drawback is that it fires the code on hover instead of on click, however the pop up box will allow the user to decide if they want to follow said link. I'll keep looking at it and see if I can find a work aorund for the click, but I think it's progressing as it will fire even when fully protected. I am using Excel 2010 if that helps.