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
HYPERLINKformula than it would be a concern that users might change your formula. If you generate it through VBA... doesn't seem much a concern as it is generated dynamically every time the dropdowns change without the user having opportunity to change the script. – nbaylyVLOOKUPformula for the Hyperlink? – EEM