Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Macro for creating hyperlink

  1. #1
    Join Date
    13th September 2004
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    4,032
    I recorded this line of code

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th September 2004
    Posts
    12
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th February 2012
    Location
    West Wales
    Posts
    9

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    4,032

    Re: Macro for creating hyperlink

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



    VB:
    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!

  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Creating One Hyperlink At A Time In Vba
    By jeddell in forum EXCEL HELP
    Replies: 4
    Last Post: April 24th, 2013, 04:58
  2. Creating hyperlink to macros
    By jsmith in forum EXCEL HELP
    Replies: 2
    Last Post: July 25th, 2006, 00:40
  3. Creating a text hyperlink
    By runningcatdog in forum EXCEL HELP
    Replies: 2
    Last Post: April 9th, 2006, 08:28
  4. Replies: 1
    Last Post: April 22nd, 2004, 02:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno