Insert Hyperlink from External Source to a Protected Sheet

[emcinco]

Hello! I have a worksheet with collection of pictures hyperlinked to an external folder. I need a VBA Code that enables me to “Insert - - > hyperlink” even if my worksheet is password protected. The worksheet was made for multiple users that is why some ranges need to stay protected while I insert my hyperlink. ‘ Insert à hyperlink’ option is greyed out in protected sheets. I tried using the code below but it did not fix my problem.

Private Sub Workbook_Open()
Sheet1.Protect Password:="password", DrawingObjects:=True, contents:=True, Scenarios:=True, userinterfaceonly:=True
Sheet1.EnableAutoFilter = True
Can someone help me out?

[IBihy]

Hello,

I assume, the code above is not the whole story. Have you tried Tools --> Protection --> Allow Users to Edit Ranges to exclude ranges from protection?

Regards,
Isabella

[mancubus]

ifyou like, you may add a macro of your own instead of InsertHyperlink proc.


create a commandbutton in sheet1 and assign the below code (code goes to sheet1's code module. double click the button in design mode or Alt+F11.)

[vba]
Private Sub CommandButton1_Click()
Sheet1.Unprotect Password:="password"
Call InsertHyperlink
Sheet1.Protect Password:="password"
End Sub
[/vba]


to insert a hyperlink to a cell.
goes to standard module:

[vba]
Sub InsertHyperlink()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=237
'Puts a dynamic hyperlink formula in the active cell. Formula automatically adjusts if its target is moved.
Dim cel As Range
Dim flPath As String, frmla As String, friendly As String

'Select a target cell in this workbook, or any other open workbook
On Error Resume Next
Set cel = Application.InputBox("Please select the target cell you want to link to", _
Title:="Hyperlink Function Builder", Type:=8)
If Err <> 0 Then Exit Sub 'If user hits 'Cancel', then an error occurs--and sub is exited
On Error GoTo 0

'Build strings for the "friendly name" displayed in the link, and for the dynamic HYPERLINK formula
friendly = InputBox("Please enter the text you want displayed in the cell containing the link", _
Title:="Hyperlink Function Builder", Default:=cel.Address(external:=True))
'This next statement avoids an error if the target workbook hasn't been saved
If cel.Parent.Parent.Path <> "" Then flPath = cel.Parent.Parent.Path & Application.PathSeparator
frmla = "=HYPERLINK(""[" & flPath & cel.Parent.Parent.Name & "]"" & " & _
"CELL(""address"",'" & cel.Parent.Name & "'!" & cel.Address & "),""" & friendly & """)"

ActiveCell.Formula = frmla 'Put the HYPERLINK formula in the active cell
End Sub

[/vba]

[Kenneth Hobs]

If that is saved as a shared workbook, you will have to unshare it to unprotect it.

Otherwise, here is another example to protect, unprotect activesheets.
[VBA]Sub UnprotectActiveSheetPWken()
UnprotectActiveSheet
End Sub

Sub ProtectActiveSheetPWken()
ProtectActiveSheet
End Sub

Sub UnprotectActiveSheet(Optional pw As String = "ken")
ActiveSheet.Unprotect pw
End Sub

Sub ProtectActiveSheet(Optional pw As String = "ken")
ActiveSheet.Protect pw
End Sub[/VBA]