Hyperlinks

[sdrane]

Does anyone know how you can enable the insertion of hyperlinks in
protected worksheet?

Thanks in anticipation
Stev

 

[Dave Peterson]

Can you leave the cell unlocked?

If yes, it might be easiest to just use a worksheet formula:

=hyperlink()

 

[sdrane]

Hi Dave and thanks for your response.

The sheet is protected and the cell is unlocked, and has other data i
it that refers to a customer name. I want to attach a hyperlink to thi
same field, which I can, if the sheet is unprotected, but that i
liklely to lead to other problems with the sheet if a user deletes
cell elsewhere.

I suppose I need code that enables the insertion of a hyperlink in
protected sheet, similar to the code I found that enables me to filte
in a protected sheet. eg

Sub Worksheet_Activate()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

End Sub

However, being a novice I have tried every combination I can think o
eg ActiveSheet.EnableHyperlink = True etc and these all fail. Can yo
help?

Thanks
Stev

 

[Dave Peterson]

I think having your code unprotect the sheet, and insert the link, then
reprotect it will work ok.

Or you can take advantage of that userinterfaceonly parm:

Option Explicit
Sub testme()

With Worksheets("sheet1")
.Protect userinterfaceonly:=True
End With

End Sub

Sub addlink()
Dim myString As String

With ActiveCell
If LCase(.Parent.Name) <> "sheet1" Then
Exit Sub
Else
myString = InputBox(Prompt:="Enter address")
If Trim(myString) = "" Then
Exit Sub
Else
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:="http://" & myString, TextToDisplay:=myString
End If
End If
End With
End Sub

But you'll have to add some checks to see if they typed in a good URL.

 

[Dave Peterson]

When I was testing that routine (xl2002), the cell didn't get a value when the
worksheet was protected.

Maybe this slight change would workbetter:

Option Explicit
Sub testme()

With Worksheets("sheet1")
.Protect userinterfaceonly:=True
End With

End Sub

Sub addlink()
Dim myString As String

With ActiveCell
If LCase(.Parent.Name) <> "sheet1" Then
Exit Sub
Else
myString = InputBox(Prompt:="Enter address")
If Trim(myString) = "" Then
Exit Sub
Else
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:="http://" & myString, TextToDisplay:=myString
ActiveCell.Value = myString
End If
End If
End With
End Sub

The activecell.value was added.

 

[Dave Peterson]

I was getting some weird results with this. The value got update, the link got
made, but whenever I clicked anywhere else, I got an error message.

If it works for you, ok. But if it doesn't, I'd go back to unprotecting, adding
the link, and then reprotecting.