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