Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Hyperlink problem (space or &) (Excel All)

    Hi,

    Please review this workbook. I have a macro called CreateIndex. It inserts a sheet into the WB, and proceeds to create a hyperlink to reference each sheet in the WB. My problem is that it seems if a worksheet has spaces or an &, it says Reference not valid when I click on the created hyperlinks to those sheets. In the hyperlink edit dialog I see single apostrophes around these sheets with spaces or symbols. How do I create this in the macro so the reference to the sheet is valid when the person follows the hyperlink.

    TX
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink problem (space or &) (Excel All)

    Put single quotes around the name:
    <code>
    Sheets("WBIndex").Hyperlinks.Add _
    Anchor:=Selection, _
    Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", _
    TextToDisplay:=Sheets(i).Name
    </code>
    The quotes are required for sheet names with spaces, just as in formulas. They do no harm in sheet names without spaces.

  3. #3
    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: Hyperlink problem (space or &) (Excel All)

    Add the single apostrophe to them all. It doesn't hurt to have it if you don't need it, but it fails when you need it

    <pre>Address:="", SubAddress:=<font color=red>"'" & </font color=red>Sheets(i).Name & "<font color=red>'</font color=red>!A1", _ </pre>



    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hyperlink problem (space or &) (Excel All)

    Great that is working well. (I experimented with the quotes, but I put the last quote in the wrong position and it debugged the whole time)!

    Observation:
    There are some hidden and very hidden sheets in the workbook. These are also listed in the hyperlinks. I inserted a line:
    If Sheets(i).Visible = True Then
    ....
    End If

    This solved this problem.

    PS: Interesting to note that a Public Sub Module got listed in the Index list too???? Very weird! The If statement sorted it out though too!

    Tx again Hans and Steve!
    Regards,
    Rudi

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hyperlink problem (space or &) (Excel All)

    <hr>Interesting to note that a Public Sub Module got listed in the Index list too<hr>
    that sounds very strange to me! Where did the hyperlink take you? (are you sure it wasn't just a coincidence of names?)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hyperlink problem (space or &) (Excel All)

    Very weird to me too...The hyperlink went nowhere, but gave an excel error dialog: Reference not valid! (Just like with the sheets that had spaces...)

    Look at this image. I took the if statement out to create the error...
    Attached Images Attached Images
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink problem (space or &) (Excel All)

    The workbook might be corrupt, I think it would be a good idea to copy the valid sheets and modules to a new workbook.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hyperlink problem (space or &) (Excel All)

    How do you determine that?
    Regards,
    Rudi

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hyperlink problem (space or &) (Excel All)

    From the weird behaviour! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I take it you didn't insert any macro sheets? (we can't really test as your posted workbook is clearly not the same as the one you are using).
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hyperlink problem (space or &) (Excel All)

    No there is no macro sheets. Its not really a problem, as the workbook I was testing it on is an old copy that a customer is not using anymore.
    Cheers
    Regards,
    Rudi

Posting Permissions

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