Selected Answer
I didn't understand what problem you have after removing the screen tip. Therefore I decided to explain the whole thing to you and you will be able to help yourself. Start with understanding that a hyperliknk has five properties as follows. Anchor (the cell in which it resides), Address (like a workbook), Subaddress (like the sheet and cell in a workbook), TextToDisplay (what to show in the cell where it resides) and ScreenTip. The following code creates a hyperlink in A10 of your active worksheet.
Private Sub SetHyperlink()
With ActiveSheet
.Hyperlinks.Add Anchor:=.Cells(10, 1), _
Address:=.Parent.Name, _
SubAddress:=.Name & "!" & Cells(10, 3).Address, _
TextToDisplay:="Link", _
ScreenTip:="Test this link"
End With
End Sub
As you see, the link leads to cell C10 in the active sheet, just 2 columns away from the link itself.
The following procedure lets you read the properties of this or any other hyperlink. Click & Hold on the hyperlink. Then run the code. You will see a message box. If there is no hyperlink in the clicked cell nothing will happen.
Sub DisplayHyperlinkProperties()
Dim Txt As String
On Error GoTo ErrExit
With ActiveCell.Hyperlinks(1)
Txt = "Address = " & .Address & vbCr & _
"SubAddress = " & .SubAddress & vbCr & _
"TextToDisplay = " & .TextToDisplay & vbCr & _
"ScreenTip = " & .ScreenTip
End With
MsgBox Txt, vbInformation, "Hyperlink properties"
ErrExit:
End Sub
The next procedure is a little bit more complicated. It allows you to modify any hyperlink but you have to set it up first. Click & Hold on the link and run the code. You can't do that with F5. Please read on for instructions. And you shouldn't do it before you know what to change. Please look at the code.
Sub ModifyHyperlink(Target As Range, _
Optional Del As Boolean)
With Target.Hyperlinks(1)
If Del Then
.Delete
' Target.Value = "No more link"
Else
' .Address = ""
' .SubAddress = ""
' .TextToDisplay = ""
.ScreenTip = "New screen tip"
End If
End With
End Sub
First, there is the 'Del' argument. If it is omitted in the procedure call if will not be True. But if it is True the hyperlink will be deleted. In that case the cell will still show the TextToDisplay. So, if you un-Rem the line to set the 'Target.Value' you can set any other cell value you want or just "".
I have remmed out all the properties except the ScreenTip. The above code will give a new value to ScreenTip. If you want to remove it set it to "". You seeem to have a problem with the cell Value. If you retain the hyperlink that would be the TextToDisplay, and of course it would copy to other cells. With the above code you can change it while keeping the link or deleting it.
The following code is for calling the 'ModifyHyperlink' procedure. Select & Hold the hyperlink and run this code. It will call the procedure just explained which will work on the hyperlink in the selected cell..
Private Sub Try()
If ActiveCell.Hyperlinks.Count Then ModifyHyperlink ActiveCell, True
End Sub
All of the above code should be in a standard code module. The next, and last, piece of code should go in the code sheet of the worksheet on which you have the hyperlinks.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Hyperlinks.Count Then
ModifyHyperlink Target, False
Cancel = True
End If
End Sub
This is a more convenient way of calling the procedure 'ModifyHyperlinks'. Click & Hold the hyperlink you want to modify and then double-click. Make sure you have set up the 'ModifyHyperlink' procedure correctly before you do. Observe that a argument (False) is passed to the 'Del' parameter. The code doesn't require it because 'Del' is optional and False by default.
All the above code in also in the attached workbook.
