New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

changing sheet names with hyperlinks intact

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

changing sheet names with hyperlinks intact

Postby annelee980 » Fri Dec 30, 2011 11:40 pm

I have a sheet with hyperlinks to other cells on the same sheet and I would like to rename the worksheet (not the workbook, just the sheet). However when I do that, the hyperlinks are no longer valid, I receive an error message and all links are reset to cell A1. Is there any way to rename the worksheet without "breaking" the hyperlinks?
  • 0

annelee980
Rookie
 
Posts: 4
Joined: Dec 30, 2011
Reputation: 0

Re: changing sheet names with hyperlinks intact

Postby k0st4din » Sat Dec 31, 2011 1:55 am

Hi and welcome to the forum.
Before a small sample simple example -> sheet1 and sheet2
On sheet1 put hiperlink to sheet2, sheet1 and then changed his name again and went into sheet2 of things I mentioned. Should be in you.
Merry Christmas
  • 0

So ...
1. *Consider well what is your wish.
2. *Make a sample table
3. *Describe in detail what you want to happen is to be understood, not only by yourself.
4. And if you help someone else who needs help. :) ;)
k0st4din
Excel Junkie
 
Posts: 217
Joined: Dec 7, 2011
Location: UK
Reputation: 3
Excel Version: 2003, 2007

Re: changing sheet names with hyperlinks intact

Postby Sisyphus » Sat Dec 31, 2011 7:33 am

Hi,
The bad news is that Excel seems to hard-code the sheet name into the hyperlink. You change the name and Excel won't know how to find the address any more, regardless of whether it is on the same or another sheet. You can avoid this problem by naming the target range instead of using A1 references. When you rename the sheet Excel updates the address of named ranges - but not hyperlinks. So, if your hyperlink was pointed at a named range it will continue to work regardless of the name of the sheet.

The good news is that the old sheet name is still in the Hyperlink and not, as Excel otherwise likes to do, replaced by #REF#. Therefore you can go to each Hyperlink and edit it. Just change the old name for the new one.

The best news is that I tried this out and, having gone that far, decided to write a little procedure for you that does the replacement job for you. here it is:
Code: Select all
Option Explicit
Option Base 0
   
Private Sub ChangeHyperLinks()

    Const OldSheetName As String = "Old Name"
    Const NewSheetName As String = "New Name"

    Dim Ws As Worksheet
    Dim Hlink As Hyperlink
    Dim SplitName() As String
   
    Set Ws = ActiveSheet
   
    For Each Hlink In Ws.Hyperlinks
        With Hlink
            SplitName = Split(.SubAddress, "!")
            If UBound(SplitName) > 0 Then
                If InStr(1, .SubAddress, OldSheetName, vbTextCompare) Then
                    .SubAddress = "'" & NewSheetName & "'!" & SplitName(1)
                End If
            End If
        End With
    Next Hlink
End Sub


- Open the VB Editor (Ctrl_F11)
- Paste the above code into the ThisWorkbook module
- Now change the name of your worksheet
- Assign the previous name of the sheet to the Constant OldSheetName at the top of the procedure
(write in the place of "Old Name")
- Assign the name of the new sheet to the Constant NewSheetName at the top of the procedure
(write in the place of "New Name")
- While the cursor is still somewhere within the procedure, press F5

Now, the code has changed all addresses in the ActiveSheet (the last sheet you looked at before opening the VB Editor) to point at the new sheet name, provided they were pointed at the old sheet name before. If they were pointed at other sheets they will not have been touched.

Now you tell me, is that a happy end to the old year or a happy start into the new? :D
Happy New Year!
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: changing sheet names with hyperlinks intact

Postby annelee980 » Sat Dec 31, 2011 1:42 pm

Wow - amazing! You have just saved me a huge amount of time. Thank you so much - it worked beautifully! Happy New Year to you - I hope yours is as good as mine will be now! :) :) :) :)

- A
  • 0

annelee980
Rookie
 
Posts: 4
Joined: Dec 30, 2011
Reputation: 0

Re: changing sheet names with hyperlinks intact

Postby annelee980 » Sat Dec 31, 2011 2:00 pm

Ok, now that the sheet name change works :) ... is it possible that there is another VBE code for keeping hyperlinks intact when adding rows to the sheet. You mentioned not using an "a1" reference in the hyperlink, but they are already built with that type of referencing.

#1 - Is there a way to relink those "a1" type references when I add lines?

#2 - I am not sure what you meant when you say to hyperlink a named range instead of the "a1" referencing type - could you please explain that?

Once again, thank you so much for your help!
  • 0

annelee980
Rookie
 
Posts: 4
Joined: Dec 30, 2011
Reputation: 0

Re: changing sheet names with hyperlinks intact

Postby Don » Sat Dec 31, 2011 4:11 pm

Hyperlinking a named range instead of A1 style cell refernces just means that you would name the desired cell; select it, go to the name box located next to the formula bar, and then type in a name that will be used to reference that cell and hit enter. Then, when you go to insert a hyperlink, select the option "Place in This Document" and you will see a list of named ranges to which you can link listed below the list of worksheets in the workbook.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: changing sheet names with hyperlinks intact

Postby Sisyphus » Sat Dec 31, 2011 11:57 pm

Hi,
You may have noticed the Split() function in my code. It splits the hyperlink target into Sheet and A1 reference. That looks like this "Sheet1!B5". When the code splits the string at the exclamation point two strings result, "Sheet1" and "B5". So far, we have replaced the first part and joined it bak to the second. The following code expands on that and allows you to change either part:
Code: Select all
Private Sub ChangeHyperLinks()

    ' Set NewSheetName same as OldSheetName to effect no change
    Const OldSheetName As String = "Sheet2"
    Const NewSheetName As String = "Sheet2"
   
    ' Keep the old or new address BLANK to effect no change
    Const OldAddress As String = ""
    Const NewAddress As String = ""

    Dim Ws As Worksheet
    Dim Hlink As Hyperlink
    Dim SplitName() As String
    Dim Changed As Boolean
   
    Set Ws = ActiveSheet
   
    If Len(OldSheetName) = 0 Or Len(NewSheetName) = 0 Then Exit Sub
    For Each Hlink In Ws.Hyperlinks
        Changed = False
        With Hlink
            SplitName = Split(.SubAddress, "!")
            If UBound(SplitName) > 0 Then
                If InStr(1, SplitName(0), OldSheetName, vbTextCompare) Then
                    If StrComp(OldSheetName, NewSheetName, vbTextCompare) Then
                        SplitName(0) = "'" & NewSheetName
                        Changed = True
                    End If
                End If
                If StrComp(Trim(SplitName(1)), OldAddress, vbTextCompare) = 0 _
                   And Len(OldAddress) And Len(NewAddress) Then
                    SplitName(1) = NewAddress
                    Changed = True
                End If
                If Changed Then .SubAddress = SplitName(0) & "!" & SplitName(1)
            End If
        End With
    Next Hlink
End Sub

You can use this code to point all hyperlinks in the currently active worksheet to a new address. If only rows were added the sheet names for old and new should be the same (nothing will be done if either old or new sheet isn't specified). Note that in Excel 2003 the address can't be specified with absolute references. Even if you enter $B$4 the Subaddress will show "B4". That makes life esy for this procedure. :D

Besides changing the address, the above code can do all that the one I gave you yesterday could. The attached workbook has the same code in its ThisWorkbook module.

Next time, please ask your second question in a separate thread. I suspect Don will move this last exchange to a new thread to keep his forum tidy. Thank you. :D
Happy New Year!
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: changing sheet names with hyperlinks intact

Postby rlong0112 » Fri Feb 27, 2015 11:40 am

Used this VB code with success. Thanks!
  • 0

rlong0112
Rookie
 
Posts: 1
Joined: Feb 27, 2015
Reputation: 0
Excel Version: Excel 2013

Re: changing sheet names with hyperlinks intact

Postby brincats » Fri Nov 04, 2016 5:27 pm

Hi Sisyphus,

Thanks for the great script for changing hyperlinks, is there a way to change only hyperlinks in column "J" only as the script below is changing all my hyperlinks in my sheet?

Thanks in advance,

Stephen

Option Explicit
Option Base 0

Private Sub ChangeHyperLinks()

Const OldSheetName As String = "Old Name"
Const NewSheetName As String = "New Name"

Dim Ws As Worksheet
Dim Hlink As Hyperlink
Dim SplitName() As String

Set Ws = ActiveSheet

For Each Hlink In Ws.Hyperlinks
With Hlink
SplitName = Split(.SubAddress, "!")
If UBound(SplitName) > 0 Then
If InStr(1, .SubAddress, OldSheetName, vbTextCompare) Then
.SubAddress = "'" & NewSheetName & "'!" & SplitName(1)
End If
End If
End With
Next Hlink
End Sub
  • 0

brincats
Rookie
 
Posts: 1
Joined: Nov 4, 2016
Reputation: 0
Excel Version: 2016


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 81 guests