Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Edit hyperlinks

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Reggie
    i'm not quite sure what you mean, but this macro will search the workbook for names and will make a hyperlink to each name in 1. worksheet column A

    Sub MakelinksToName()
    For Each n In ActiveWorkbook.Names
    x = x + 1
    Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
    n.Name
    Next
    End Sub

    regards Tommy

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Reggie
    This macro will limit the names to those on sheet 2

    Sub MakelinksToName()
    For Each n In ActiveWorkbook.Names
    If InStr(1, n.RefersTo, Sheets(2).Name) Then
    x = x + 1
    Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
    n.Name
    End If
    Next
    End Sub

  3. #13
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thankyou Tommy. Works well.

    I was wondering if this coding can be modified so that it limits the creation of hyperlinks to names located in a particular column in sheet 2? My sheet has many hundreds of names and limiting to a particular row would be helpful.

    Thankyou again very much for your help.

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Reggie
    Modified code. Run this while being on sheet 1. This will ask you for a column. Just point to sheet 2 and a select a cell in the column you want to list hyperlinks for.


    Sub MakelinksToName()
    Dim c As Range
    Set c = Application.InputBox("Which column ?", , , , , , ,
    col = c.Column
    For Each n In ActiveWorkbook.Names
    If InStr(1, n.RefersTo, Sheets(2).Name) Then
    Set t = Range(Mid(n.RefersTo, Len(Sheets(2).Name) + 3))
    If t.Column = col Then
    x = x + 1
    Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
    n.Name
    End If
    End If
    Next
    End Sub

    regards Tommy

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In a line I got a smiley. Change to:
    Set c = Application.InputBox("Which column ?", , , , , , , 8 )

  6. #16
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thankyou very much Tommy.

    Your help has saved me a lot of time and frustration.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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