Expert: Pat Murphy - 8/27/2004

Question
Hi,

I'm a novice with VB but so far I've been able to work most things out for myself...now I'm a bit stuck:

I'm creating a simple company database in Excel 2003 (VB v6.3) where each worksheet is a company record (the name of each worksheet is the company name). One worksheet is set aside as an "Overview" page displaying key information of all the companies. The Company Names on the Overview sheet are hyperlinked so by clicking on them you jump straight to the corresponding sheet with that specific company record.

I want the links to be created automatically when a new record is entered. As far as I can tell, the code to create a hyperlink say in cell B3 is as follows:

Range("B3").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, address:="", SubAddress:= "'companyname'!A1", TextToDisplay:="Sheet1"

Because I want the links created automatically I need to be able to specify the SubAddress ("'companyname'!A1" in this case) by a variable (which I assume would be a Range variable). How would I go about doing this?

Answer
Based on THis Article:
http://www.microsoft.com/exceldev/articles/ch11.htm

It is even easier than this:
Proc01, which follows, adds two new hyperlinks to Sheet2 that point to Microsoft's Web site. One is attached to a Range object and the other to a Shape object:

Sub Chap11aProc01_AddHyperlink()
   With ThisWorkbook.Sheets(2)
       .Activate
       .Hyperlinks.Add Range("A1"), "http://www.microsoft.com"
       .Shapes.AddShape msoShapeExplosion2, 45, 55, 90, 45
       .Hyperlinks.Add .Shapes(1), "http://www.microsoft.com"
   End With
End Sub