dcsimg
Results 1 to 8 of 8

Thread: [EXCEL VBA] Break links

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2003
    Location
    Newark-on-trent, Nottingham
    Posts
    243

    [EXCEL VBA] Break links

    Hi

    I have a bit of VBA that copies a sheet from the active spreadsheet to a new spreadsheet and saves it locally for the user - this all works fine!

    However when you open the new spreadsheet the hyperlinks in the sheet copied no longer work and give the user an error 'Reference isn't valid.'.

    The hyperlinks are setup to take the user to a specific cell in the document rather than an external URL and appear to be referencing the original spreadsheet when you hover over the hyperlink.

    Name:  Capture1.jpg
Views: 76
Size:  24.3 KB

    Name:  Capture2.JPG
Views: 73
Size:  26.3 KB

    Anyone know how i can resolve this in my VBA code?
    Last edited by stephena; Jan 14th, 2019 at 08:13 AM.

  2. #2
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,229

    Re: [EXCEL VBA] Break links

    What do the links look like in the original sheet?

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,551

    Re: [EXCEL VBA] Break links

    I believe it would work if you used Defined Names rather than a Cell Reference.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Oct 2003
    Location
    Newark-on-trent, Nottingham
    Posts
    243

    Re: [EXCEL VBA] Break links

    Quote Originally Posted by jdc2000 View Post
    What do the links look like in the original sheet?
    When you right click and edit the hyperlink in the new spreadsheet the Cell reference changes to A1 and you get the error previsouly mentioned.

    I wondered if there was a way to setup the hyperlinks when the page opens... its always a link to a cell on the same row but the hyperlink only occurs once ever 20 rows.

    Thanks

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,929

    Re: [EXCEL VBA] Break links

    you can iterate through all hyperlinks and remove the broken ones and re-add

    this is not really tested, but looks like it should work correctly, assign the correct worksheet to sh worksheet object
    Code:
    For Each h In sh.Hyperlinks
        t = h.TextToDisplay
        sa = h.SubAddress
        n = h.Name ' not used here
        a = h.Range.Address
        h.Delete
    Next
    sh.Hyperlinks.Add Range(a), "", sa, , t
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,551

    Re: [EXCEL VBA] Break links

    I tested it. Works like a charm!

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,929

    Re: [EXCEL VBA] Break links

    I tested it. Works like a charm!
    actually adding the hyperlink should be done within the loop, else only the last one would be readded

    if removing and adding multiple hyperlinks stuffs up the collection indexes, then it would pay to add the hyperlinks to be removed to an array or collection then re-adding the hyperlinks using values from arrays of variables
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,551

    Re: [EXCEL VBA] Break links

    Good point. I tested with only one hyperlink on the sheet.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width