Announcement

Collapse
No announcement yet.

Macro for creating hyperlink

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro for creating hyperlink

    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

  • #2
    I recorded this line of code

    Code:
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Sheet2!A1"
    Then taking this one line of code I developed the following routine
    Code:
    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
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      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

      Comment


      • #4
        Re: Macro for creating hyperlink

        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
        Live the Life you Dream, Retro Retreats

        Comment


        • #5
          Re: Macro for creating hyperlink

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



          Code:
          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.
          Regards,
          Barry

          My Favorite New Thing:
          Dynamic Named Ranges



          The alternative for
          "Press Any Key To Continue."

          and we all have one we'd like to use it on

          1. Cross Posting Etiquette
          2. Are You Here To Learn: What Have You Tried?
          3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

          Comment


          • #6
            Re: Macro for creating hyperlink

            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,
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment

            Working...
            X