Macro for creating hyperlink

[pethe]

I have written a macro which creates a new sheet . Sheet Name is taken from the user via apop up window . This name is then assigned to sheet.
The macro then picks up this sheet name and assigns to a cell in the newly creates sheet.

I have another macro which creates a list of all the sheets in a coloumn. I want that each cell that has a new sheet name should be hyperlinked to the relevant sheet so that when the user clicks the cell in which the sheet name is present he is displayed the sheet directly


Any idea how can i hyperlink the contents of a cell to map to a particular sheet name using a macro


regards
..pethe

[iwrk4dedpr]

I recorded this line of code

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Sheet2!A1" 

Then taking this one line of code I developed the following routine

 
Sub HyperLink_SheetsToIndex() 
     
     '   Local Variables
    Dim wks                 As Worksheet 
    Dim rngLinkCell         As Range 
    Dim strSubAddress       As String, strDisplayText       As String 
     
     '   Step 1 : Loop through all worksheets
     ' 1a : Clear all current hyperlinks
    Worksheets("Sheet Index").Range("A:A").ClearContents 
     ' 1b : Create Linked index list
    For Each wks In ActiveWorkbook.Worksheets 
        Set rngLinkCell = Worksheets("Sheet Index").Range("A65536").End(xlUp) 
        If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0) 
        strSubAddress = "'" & wks.Name & "'!A1" 
        strDisplayText = "HyperLink : " & wks.Name 
        Worksheets("Sheet Index").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText 
    Next wks 
     
End Sub 

Paste this routine into a module page. Insert a worksheet or rename a current worksheet to "Sheet Index" and it'll list all the Worksheets in the current workbook. It doesn't get the Chart sheets.

Regards,
Barry

[pethe]

Thanks Barry
You code executes just the way i wanted it to !!!


Man this forum is really such a great place to learn things I am very glad to be part of this forum


Thanks to all who are a part of this wonderfull forum - you made my life easy


regards
..pethe

[Chewy73]

how can you specify the start cell for the above macro as it does everything I want except I need it to start populating the worksheet from B15

[iwrk4dedpr]

Wow Chewy ... kinda an old thread but....

Sub HyperLink_SheetsToIndex() 
     
     '   Local Variables
    Dim wks                 As Worksheet 
    Dim rngLinkCell         As Range 
    Dim strSubAddress       As String, strDisplayText       As String 
     
     '   Step 1 : Loop through all worksheets
     ' 1a : Clear all current hyperlinks
    Worksheets("Sheet Index").Range("A:A").ClearContents 
     ' 1b : Create Linked index list
    For Each wks In ActiveWorkbook.Worksheets 
        Set rngLinkCell = Worksheets("Sheet Index").Range("B65536").End(xlUp) 
        If Worksheets("Sheet Index").Range("B15") = "" Then 
            Set rngLinkCell = Worksheets("Sheet Index").Range("B15") 
            EndIf 
            If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0) 
            strSubAddress = "'" & wks.Name & "'!A1" 
            strDisplayText = "HyperLink : " & wks.Name 
            Worksheets("Sheet Index").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText 
        Next wks 
         
    End Sub 

This will work ... just don't have time to make the code cleaner.

[AAE]

Chewy73,

We're glad to have you here, however, it is against forum rules to ask questions in threads started by others. (You did read the rule, right?)

Always start a thread for your own questions and if you find it helpful include a link to any other thread you deem helpful to provide clarity.

Thanks,