Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Edit hyperlinks

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha,

    I renamed my worksheets in a workbook not realizing that it breaks the hyperlinks. I tried using "replace" but that does not seem to work. I can't rename the the sheets, as I have other formulas with the new worksheet names. Anyone have any ideas, besides doing it one by one?

    Brian

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 11:00, Brian from Maui wrote:
    Aloha,

    I renamed my worksheets in a workbook not realizing that it breaks the hyperlinks. I tried using "replace" but that does not seem to work. I can't rename the the sheets, as I have other formulas with the new worksheet names. Anyone have any ideas, besides doing it one by one?

    Brian
    Are your hyperlinks to other pages within the workbook? As far as I know, any formulas that have sheet tab names in them will automatically update when you change a sheet tab name.

    If your hyperlink is to another named range in your workbook, then changing the sheet name again will automatically update the reference to the named range.

    Regards, Duane
    Regards, Duane
    Office2010 in Win7

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mate

    Ermm you changed only the sheet name or WrkBk name?? Should auto amend, or haver i lost the plot/// Idea

    Chjhange the name back, if can reememebr te file name file open lists the docd or windows recent or start docs will give them away so full name ther.

    No back up eh! now now, you read all my posts i know and how often do i scream BACK UP BACK UP BACK UP! so you could play and carry on on original without agro until FULLY solved.

    Also set up another sheet TEST DUMY links oe somethings and play till you get it right, i never work live (thats on the critical current sheet UNLESS its fullt tested)

    Also ... no its should be all oK...


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well Brian, I feel your pain, my links don't seem to update when I change a sheet name. But here's some vba to generate links, not sure if it helps.

    Sub Workshts()
    Dim ws As Worksheet, wsTOC As Worksheet
    Dim r As Integer, c As Integer
    Application.ScreenUpdating = False
    Set wsTOC = ActiveSheet
    r = ActiveCell.Row
    c = ActiveCell.column
    For Each ws In ActiveWorkbook.Worksheets
    wsTOC.Hyperlinks.Add _
    Anchor:=wsTOC.Cells(r, c), _
    Address:="", _
    SubAddress:=ws.Name & "!A1", _
    TextToDisplay:=ws.Name
    r = r + 1
    Next
    Application.ScreenUpdating = True
    End Sub

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Duane,

    Couldn't see the tree through the forrest again!

    Brian

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

    Default

    As a bit of an add-on to the previous question, I was wondering whether someone could help me adjust the code provided by NateO so that it creates the hyperlinks based on names created in another sheet.

    Assmume sheet2 has defined names ranging from Answer1 to Answer100, I want on sheet1 to automatically hyperlink to these names. The cells in Sheet1 will have Answer1 to Answer100 typed in them. Just a bit tedious manually placing them all in.

    I tried playing around with it a little, but my low level of VB knowledge makes it a long road.

    Any help would be greatly appreciated. Thankyou.

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha,

    Just curious, can you edit the hyperlinks with something like "replace", or is it one by one?

    Brian

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

    Default

    Not sure whether replace can be used here.

    Say I have the following on sheet1:


    A
    Answer1
    Answer2
    Answer3
    Answer4
    .......
    Answer100

    Each of these contains a hyperlink which refers to the same name in sheet2.

    Not sure how I would do this using replace, but you maybe on to something. Thanks for that anyway mate.

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried to use replace but that doesn't seem to work, or am I missing something again. Like Duane said I can always change the sheet names back to what the hyperlinks had but I was just curious if it could be done without doing each hyperlink one by one or by changing the sheet name to the original

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

    Default

    This macro will change the destinationaddress from the old sheetnames to the new ones.
    Just write the old sheetnames in column E and beside them in column F the new name of the sheet.
    Adjust the range in the macro according to your needs.

    Sub ChangeHyperlinks()
    Dim newones As Variant
    newones = Sheets(1).Range("e1:f3")
    Dim rSH As Worksheet
    For Each rSH In ActiveWorkbook.Worksheets
    For Each h In Worksheets(rSH.Name).Hyperlinks
    g = h.SubAddress
    For x = 1 To UBound(newones)
    If InStr(1, g, newones(x, 1)) Then h.SubAddress = Replace(g, newones(x, 1), newones(x, 2))
    Next
    Next
    Next
    End Sub


    regards Tommy

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
  •