PDA

View Full Version : How to make Hyperlink which will change with cell number?



jiura
04-26-2008, 04:47 AM
Hello to all!
There is one cell on Excel sheet witch has Hyperlink on itself (yes! on itself, beciouse I need handpoint as mouse Iconabove this cell). But user can add or del some cells above this cell. Becouse of this Hyperlink doesn't follow this cell number:
For example If my cell has Hyperlink on "C25" and then user Add two rows, it has Hyperlink on "C25" not on "C27", but I need Hyperlink on "C27"

Bob Phillips
04-26-2008, 05:09 AM
Try adding an Excel Defined name to that cell, and then change the link in the hyperlink to the Defined Name rather than the cell reference.

jiura
04-26-2008, 05:17 AM
Yes this is good, But I have a lot of such cells (they may be add or del )and I dn't want to add name to each of the cell

jiura
04-27-2008, 07:28 AM
Hello to all again!
I solve this problem, and post my solution here:


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Range.Address), Address:="", SubAddress:=Target.Range.Address, ScreenTip:="Hi!
Range(Target.Range.Address).Select
Application.ScreenUpdating = True
End Sub


Thats all! NowHyperlink is always connect with one cell and link with it, and not depends of count of cells in above and left of this cell

zamzam
06-26-2010, 02:11 PM
Dear jiura,

Can you please explain how to use your solution. I am not an expert in macros so please go easy on me.