PDA

View Full Version : Hyperlink SubAddresses lost during Save As



xstatic414
11-30-2008, 01:51 PM
With Excel 2003: I have a number of hyperlinks in a menu that self-reference to initiate a macro using:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink).

So a hyperlink clicked in cell "B2" references to "B2" to run a macro. This works great. The problem is that when I SaveAs using vba with:

ActiveWorkbook.SaveAs Filename:= _
(SupplierFileName) ' _
', FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
'ReadOnlyRecommended:=False, CreateBackup:=False

the hyperlink SubAddresses all revert to "A1". When I physically SaveAs a file with F12, the menus all work fine. The links also break when copying a sheet to another workbook

So, if any kind person can tell me how to SaveAs or Copy so they don't break, that would be wonderful.

As a workaround, I recorded a macro to physically go to each broken link and fix it, but this means a new macro for each different sheet layout. Here's the short version of what I have.

Sub Fix_Macros()

Range("B2").Select
Selection.Hyperlinks(1).SubAddress = "B2"
Range("C2").Select
Selection.Hyperlinks(1).SubAddress = "C2"
' and so on for another 15 links
End Sub

This does work, but I know that a more elegant version exists using hyperlinks.count , but I'm a hopeless programmer and can't seem to redesign the few examples I can find. So 6 hours later :banghead:, I ask you for help with either the workaround or the saveas fix.

THANKS, Kevin

lucas
11-30-2008, 02:35 PM
With Excel 2003: I have a number of hyperlinks in a menu that self-reference to initiate a macro using:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink).

So a hyperlink clicked in cell "B2" references to "B2" to run a macro.

Can you give us a sample workbook demonstrating what you are doing here......it would make it much easier to understand your problem.

xstatic414
11-30-2008, 05:12 PM
Okay, when preparing the attached workbook, I found out what is going wrong. It is actually when the sheetname is renamed, NOT the save, that the hyperlinks all re-SubAddressed to "A1". Any ideas?

david000
12-01-2008, 01:48 AM
Change B2 and C2 to named ranges like, Link1 and Link2, then make those the subaddress for each cell. That worked for me.


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Select Case Target.TextToDisplay

Case "Link 1"
Application.Run "Rename_Sheet"

Case "Link 2"
Application.Run "Rename_Sheet"

End Select
End Sub




Sub Rename_Sheet()
With Sheet2
If .Name = "Name Changed" Then
.Name = "Original Name"
ElseIf .Name = "Original Name" Then
.Name = "Name Changed"
End If
MsgBox .Name
End With
End Sub

xstatic414
12-01-2008, 04:40 PM
Seems so simple when someone else thinks of it! SOLVED with my thanks. Kevin

lucas
12-01-2008, 05:58 PM
Kevin, be sure to mark your thread solved using the thread tools at the top of the page.