Excel - Hyperlinck - Macro

[Stefaan100]

I am setting up a financial reporting system for someone. It will start out with a sheet of totals that gives an overview while the details with be in different sheets. Then I was planning on using hyperlincks that would like one line of the totals to their detail. For example, in the totals page there would be a line called "Equities X". The header for Equities X would be a hyperlinck that links to the details sheet. On the details sheet there would then be a hyperlink back to the totals sheet. The system works great up to the point when anything is changed. More specifically if I change the name of a sheet or insert or delete rows, the hyperlinks are all off. In the future it will be necessary for me to make these changes and I do not want to have to redo each of the Hyperlinks everytime I make a change. A friend told me to stop using hyperlinks and instead to use macros or visual basic. I looked at it a little and although I believe I am good with excel, I have no idea how these macros (and visual basic) work. Anyone any insights?

 

[DirkStruan]

Hi Stefaan

I think you should have posted this one in the VBA forum 707, but here#s what I would do.

First set up a user defined function to return the name of the sheets in your workbook.

Function shtname(indx)
shtname = Sheets(indx).Name
End Function

Next, on your summary page list the page numbers, 1 to n and set up shtname functions for each value listed.

Name the range of these sheet names "hypers" or something.

The add the following code to the summary sheet (right click the tabe and select view code. Change the first drop down to Worksheet (from General) and add the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsNull(Intersect(Target, Range("hypers"))) = True Then
    Exit Sub
Else
    Sheets(ActiveCell.Text).Select
End If
End Sub

Then, whenever you click on the result of the shtname function in the "hypers" range the correct page will be selected.

Hope this helps or gives you some ideas.


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?