Edit hyperlinks

Brian from Maui · Mar 19th, 2002, 12:00 PM

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

Duane · Mar 19th, 2002, 03:11 PM

Quote:

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

Jack in the UK · Mar 19th, 2002, 03:39 PM

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...

NateO · Mar 19th, 2002, 03:45 PM

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

Brian from Maui · Mar 19th, 2002, 03:46 PM

Duane,

Couldn't see the tree through the forrest again!

Brian

Reggie · Mar 19th, 2002, 09:05 PM

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.

Brian from Maui · Mar 19th, 2002, 10:16 PM

Aloha,

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

Brian

Reggie · Mar 19th, 2002, 10:28 PM

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.

Brian from Maui · Mar 19th, 2002, 10:48 PM

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

Tommy Bak · Mar 20th, 2002, 03:39 AM

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

Mar 19th, 2002, 03:39 PM	#3
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	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"

Mar 19th, 2002, 03:45 PM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 12:00 PM	#1
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 19th, 2002, 03:46 PM	#5
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Duane, Couldn't see the tree through the forrest again! Brian

Mar 19th, 2002, 09:05 PM	#6
Reggie New Member Join Date: Mar 2002 Posts: 10	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.

Mar 19th, 2002, 10:16 PM	#7
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Aloha, Just curious, can you edit the hyperlinks with something like "replace", or is it one by one? Brian

Mar 19th, 2002, 10:28 PM	#8
Reggie New Member Join Date: Mar 2002 Posts: 10	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.

Mar 19th, 2002, 10:48 PM	#9
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Mar 20th, 2002, 03:39 AM	#10
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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