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

i have worksheet has bunch of dynamic hyperlinks change based on drop down menu. cells drop down menus unlocked. have "select locked cells" unchecked when protect sheet, users can select drop down menus. unfortunately, when this, hyperlinks no longer usable.

does know how work around this?

update*

as requested, code 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 drop down user selects particular option. hyperlinks change based on selection.

2) 'data sheet' separate sheet houses of reference data in array.

this says: value in b4 match first column in data chart? if so, use hyperlink formula using vlookup insert corresponding url formula.

this understanding of settings , requirements:

settings

• there protected worksheet dropdown menu updates other cells containing vlookup\hyperlink formulas.

• all cells in worksheet, excluding dropdown menus, protected.

• the value of cells containing vlookup\hyperlink formulas, equal www address or blank depending on value of dropdown menu. such, hyperlinks point web pages or blank.

• the worksheet enableselection set xlunlockedcells determines once worksheet protected “only unlocked cells can selected.”

requirements - need maintain worksheet protected safeguard al contents including vlookup\hyperlink formulas.

• need allow users select\activate unprotected cells aesthetic reasons , deliver professional product.

this solution uses following resources

• the hyperlink function
• an udf (user defined function)
• two public variables ,
• the worksheet_beforedoubleclick event

when udf wrapped hyperlink function causes that every time mouse hovers on cell containing combined formula of hyperlink(udf,[friendlyname]) udf triggered.

we’ll use public variable hold linklocation, used later follow hyperlink upon users decision.

and second public variable set time when linklocation last updated.

we’ll mimic manner in hyperlink “normally” activated:

• by user selects cell , clicks hyperlink in selected cell.

• instead user hovers on cell hyperlink (the udf feeds linklocation , time public variables) , doubleclicks cell (triggering worksheet event follow hyperlink, validating first time when linklocation last updated ensure stills actual , clearing linklocation variable).

first need ensure formulas used in worksheet generate dynamic hyperlinks have appropriated structure:

assuming current vlookup\hyperlink formulas have following structure: (have work based on assumptions actual formula not provided)

=iferror( hyperlink( vlookup( dropdowncell , range , column, false ), friendlyname ), "" ) 

we need change formula following structure:

=iferror( hyperlink( udf( vlookup( dropdowncell , range , column, false ) ), friendlyname ), "" ) 

the following procedures take care of modifying formulas structure make them suitable solution proposed. suggest copy both in separated module named “maintenance”.

option explicit  private sub wsh_fmlhyperlinks_reset() const kwshpss string = "wshpsswrd" const khyplnk string = "hyperlink(" dim wshtrg worksheet, rhyplnk range dim rcll range, shyplnkfml string dim sold string, snew 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)) exit sub     if rhyplnk nothing exit sub      rem add hyperlinks names     each rcll in rhyplnk.cells         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 range, rout range, _     vwhat variant, elookin xlfindlookin, elookat xllookat) boolean dim rfound range, sfound1st string     rinp         set rfound = .find( _             what:=vwhat, after:=.cells(1), _             lookin:=elookin, lookat:=elookat, _             searchorder:=xlbyrows, searchdirection:=xlnext, _             matchcase:=false, searchformat:=false)         if not (rfound nothing)             sfound1st = rfound.address                             if rout nothing                     set rout = rfound                 else                     set rout = union(rout, rfound)                 end if                 set rfound = .findnext(rfound)             loop while rfound.address <> sfound1st     end if:  end     rem set results     if not (rout nothing) rng_find_set = true end function 

these public variables , udf. suggest copy them in separated module.

option explicit  public pshyplnkloct string, pdtmenow date  public function udf_hyplnklct_set(shyplnkfml string) string     pshyplnkloct = shyplnkfml     pdtmenow = end function 

and copy procedure in module of protected worksheet dynamically generated hyperlinks.

option explicit  private sub worksheet_beforedoubleclick(byval target range, cancel boolean)     if = pdtmenow , pshyplnkloct <> empty         thisworkbook.followhyperlink address:=pshyplnkloct, newwindow:=true     end if end sub