Help: Show Insert/Edit/Remove Hyperlink Option On Read Only Sheets (Locked)

[cbryan15]

Hi Excel Geniuses!

I would like to ask some help in resolving this matter. I am almost done doing this Excel project of mine. Each sheets consists of locked and unlocked cells. Whenever someone opens the file, it will automatically change it's interface to read-only, whereas, it will lock all the cells that are protected and remain unlocked all those that are not. I entered this VBA code:

Private Sub Workbook_Open()
For Each ws In Sheets
With ws
.Unprotect Password:="Secret"
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next ws
End Sub

But the problem is, Excel will not allow me to insert, edit or remove HYPERLINK on the cells that need hyperlink. Can you please help me resolve this thing? Is there a VBA code that I can use to make the hyperlink option enabled if the file is protected?

Thank you very much in advance!

 

[Worf]

Hi

But the problem is, Excel will not allow me to insert

Who is going to insert hyperlinks, you or the end user? You know the password, so it would just be a matter of unprotecting the sheet.
Anyway, for the record, it can be done with VBA:

Sub AddHyperLink()
Dim Ad$, Nm$, Tip$
      
Ad = "http://www.startrek.com"
Nm = "Star Trek"
Tip = "Click me"
With Sheet1
    .Protect Password:="secret", UserInterFaceOnly:=True
    .Hyperlinks.Add Anchor:=.Range("d4"), Address:=Ad, ScreenTip:=Tip, TextToDisplay:=Nm
End With


End Sub