Re - Excel and the HyperLink Formula

[TKT-Tang]

Having entered the following worksheet formula into cell B21 on
Sheet2,

=IF(B20="","No HyperLink",
HYPERLINK(MID(CELL("FileName",G17),SEARCH("[",CELL("FileName",G17)),
SEARCH("]",CELL("FileName",G17))-SEARCH("[",CELL("FileName",G17))+1)&CHAR(39)&
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))&CHAR(39)&CHAR(33)&"A1",
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))))

Then, enter any number into cell B20 and a Hyperlink appears in cell
B21. Erase cell B20 and the Hyperlink in cell B21 is replaced by the
text "No Hyperlink".

As the mouse cursor nears cell B21, it switches to the "palm" pointer
; that's misleading since it's not a Hyperlink-ing source anymore.

Please provide a means to nullify the recurring Hyperlink-palm in the
neighbourhood of cell B21 (until cell B20 is filled again ; that's the
right time to activate the Hyperlink-ing thereof).

Regards.

 

[Dave Peterson]

First, I think your formula can be reduced to:

=IF(B20="","",HYPERLINK("#"&CELL("address",Sheet1!A1),"Sheet1"))

But I don't think you can change that behavior with the pointer. (Well, I
couldn't!).

But you could use a workbook_change event that puts the hyperlink in the cell or
takes it out whenever that cell changed.

If you want to try this out, rightclick on the worksheet that should have this
behavior. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b20")) Is Nothing Then Exit Sub

Set wks = Worksheets("sheet1")

On Error GoTo errHandler
With Target
Application.EnableEvents = False
If IsEmpty(.Value) Then
With .Offset(1, 0)
If .Hyperlinks.Count > 1 Then
.Hyperlinks.Delete
End If
.Value = "No Hyperlinks"
.Style = "Normal"
End With
Else
With .Offset(1, 0)
.Hyperlinks.Add anchor:=.Item(1), _
Address:="", SubAddress:="#'" & wks.Name & "'!a1", _
TextToDisplay:=wks.Name
.Value = "'" & wks.Name
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

I reverted to a style of normal (it removes the blue font and underline), but it
changes it back to whatever Normal is for that workbook. You may want to be
more specific on what you're changing.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Having entered the following worksheet formula into cell B21 on
Sheet2,

=IF(B20="","No HyperLink",
HYPERLINK(MID(CELL("FileName",G17),SEARCH("[",CELL("FileName",G17)),
SEARCH("]",CELL("FileName",G17))-SEARCH("[",CELL("FileName",G17))+1)&CHAR(39)&
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))&CHAR(39)&CHAR(33)&"A1",
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))))

Then, enter any number into cell B20 and a Hyperlink appears in cell
B21. Erase cell B20 and the Hyperlink in cell B21 is replaced by the
text "No Hyperlink".

As the mouse cursor nears cell B21, it switches to the "palm" pointer
; that's misleading since it's not a Hyperlink-ing source anymore.

Please provide a means to nullify the recurring Hyperlink-palm in the
neighbourhood of cell B21 (until cell B20 is filled again ; that's the
right time to activate the Hyperlink-ing thereof).

Regards.

 

[Dave Peterson]

And Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

Having entered the following worksheet formula into cell B21 on
Sheet2,

=IF(B20="","No HyperLink",
HYPERLINK(MID(CELL("FileName",G17),SEARCH("[",CELL("FileName",G17)),
SEARCH("]",CELL("FileName",G17))-SEARCH("[",CELL("FileName",G17))+1)&CHAR(39)&
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))&CHAR(39)&CHAR(33)&"A1",
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))))

Then, enter any number into cell B20 and a Hyperlink appears in cell
B21. Erase cell B20 and the Hyperlink in cell B21 is replaced by the
text "No Hyperlink".

As the mouse cursor nears cell B21, it switches to the "palm" pointer
; that's misleading since it's not a Hyperlink-ing source anymore.

Please provide a means to nullify the recurring Hyperlink-palm in the
neighbourhood of cell B21 (until cell B20 is filled again ; that's the
right time to activate the Hyperlink-ing thereof).

Regards.

 

[TKT-Tang]

Re - GGVT TKT - Excel and the HyperLink Formula

Mr. Dave Petersen,

Thank you for responding to my query.

The addition of "#"& in the following Hyperlink construct,
=HYPERLINK("#"&CELL("address",Sheet1!A1),"Sheet1"),
is an evidence of miracle indeed.

Using "Insert Hyperlink" wizard is an efficient means to meet the
given purpose. However, the linkage would be crashed upon changing the
name of the Hyperlink-ing destination.

Resort to the worksheet formula, Hyperlink ; and there, constructing a
Hyperlink by following the given examples (verbatim) in the Excel Help
File would not bear fruit.

In retrospect, in order to pursue the capability of tracking the
changing name of Hyperlink-ing destination,

MID(CELL("FileName",G17),SEARCH("[",CELL("FileName",G17)),
SEARCH("]",CELL("FileName",G17))-SEARCH("[",CELL("FileName",G17))+1)&CHAR(39)&
RIGHT(CELL("filename",Sheet1!$A$1),
LEN(CELL("filename",Sheet1!$A$1))-
SEARCH("]",CELL("filename",Sheet1!$A$1)))&CHAR(39)&CHAR(33)&"A1",

is almost (superficially) tantamount to, CELL("address",Sheet1!A1).

The latter worksheet formula is a more efficient contender ;
obviously, it shows that the way of doing a thing should always be the
easy way. Again, thank you for sharing the magical manifestation.

Regards.