edit hyperlinks (Excel 2000)

Topic

New Reply

I have an several images in excel which links to a hyperlink. I would like to change the directory name in all of them at once with a search and replace function, like you can in the formulas? Does anyone know how to do this?

Reply | Quote

Replies

Here is a Simple find/replace. You could enhance it to ask for the old and new in an input box if desired.

Steve

Sub FixHyperlink()
    Dim wks As Worksheet
    Dim hlink As Hyperlink
    Dim sOld As String
    Dim sNew As String
    
    Set wks = ActiveSheet
    sOld = "OldPath"
    sNew = "NewPath"

    For Each hlink In wks.Hyperlinks
        hlink.Address = Application.WorksheetFunction. _
            Substitute(hlink.Address, sOld, sNew)
    Next hlink
    Set wks = Nothing
End Sub

Reply | Quote

Thanks Steve, this worked great…Susan

Reply | Quote

Hi Steve: I just had two questions relating to this macro. The first is, now I get an excel edit box every time I open the worksheet. How can I remove this? Second, I cannot remove or edit the macros once the file is saved. The edit and remove tabs are greyed out. Is there a way to remove this macro?

Thanks..Susan

Reply | Quote

What is an “excel edit box”?
Are you referring to the “macro warning – enable or disable?

This comes because of the macro.
if you want to keep this macro you can move it to your personal.xls file and run it from there instead of from this workbook.

If you have deleted it using tools – macros, the edit will be gone, since there is no macro to edit. The module will remain, you must remove the module the code was in (this is done in VB editor – alt-F11) or you will still get the warning.

Steve

Reply | Quote

What is an “excel edit box”?
Are you referring to the “macro warning – enable or disable?

This comes because of the macro.
if you want to keep this macro you can move it to your personal.xls file and run it from there instead of from this workbook.

If you have deleted it using tools – macros, the edit will be gone, since there is no macro to edit. The module will remain, you must remove the module the code was in (this is done in VB editor – alt-F11) or you will still get the warning.

Steve

Reply | Quote

Hi Steve: I just had two questions relating to this macro. The first is, now I get an excel edit box every time I open the worksheet. How can I remove this? Second, I cannot remove or edit the macros once the file is saved. The edit and remove tabs are greyed out. Is there a way to remove this macro?

Thanks..Susan

Reply | Quote

Thanks Steve, this worked great…Susan

Reply | Quote

Here is a Simple find/replace. You could enhance it to ask for the old and new in an input box if desired.

Steve

Sub FixHyperlink()
    Dim wks As Worksheet
    Dim hlink As Hyperlink
    Dim sOld As String
    Dim sNew As String
    
    Set wks = ActiveSheet
    sOld = "OldPath"
    sNew = "NewPath"

    For Each hlink In wks.Hyperlinks
        hlink.Address = Application.WorksheetFunction. _
            Substitute(hlink.Address, sOld, sNew)
    Next hlink
    Set wks = Nothing
End Sub

Reply | Quote