PDA

View Full Version : Insert Hyperlink from External Source to a Protected Sheet



emcinco
02-03-2011, 04:34 AM
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
02-09-2011, 03:55 PM
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
02-10-2011, 12:30 AM
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.)


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



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


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

Kenneth Hobs
02-10-2011, 09:36 AM
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.
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