Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    edit hyperlinks (Excel 2000)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: edit hyperlinks (Excel 2000)

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

    Steve
    <pre>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</pre>


  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: edit hyperlinks (Excel 2000)

    Thanks Steve, this worked great...Susan

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: edit hyperlinks (Excel 2000)

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: edit hyperlinks (Excel 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •