Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel 2007 HYPERLINK Character Limit

    Hi all,

    I did search for this post first, but there was only one, and it wasn't very helpful.

    I need to create a hyperlink in a formula, but am hitting the 256 character limit resulting in #VALUE.

    I don't mind going down the VBA route, in fact, I'm sure I'll have to, but I'll need a little guidance, if thats ok?

    I have data in cells b7:b40. I am essentially writing:
    =HYPERLINK("xxxxxxxx.html?&"substitute(b7," ","")&substitute(b8," ","")), etc onto B40.

    How can i:
    A) Create the above in VBA?
    B) Where in this button code do I put it?

    Private Sub CommandButton1_Click()

    WebBrowser1.Navigate2 ActiveSheet.Range("A1").Value

    End Sub

    Private Sub WebBrowser1_StatusTextChange(ByVal Text As String)

    End Sub


    Many thanks in advance, I'm learning, slowly

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does it work with only 1 substitute:
    =HYPERLINK("xxxxxxxx.html?"&substitute(B7&B8&B9&.. .&B40 ," ",""))

    Or you could create in a cell the concatenated cells and refer to that cell

    Steve

  3. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I tried splitting the string across multiple cells, then using a concatenate in my main cell of combining these up again, but I think it's what the formula results in that it doesn't like, ie, concatenate(a1,b1,c1) is well below 256 characters, but the result may be 300 characters long. #VALUE

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    How about a line of code like:
    ActiveSheet.Hyperlinks.Add _
    Anchor:=Range("A15"), _
    Address:="xxxxxxxx.html?"&Application.WorksheetFun ction.Substitute(Range("B7")&Range("B8")& ...&Range("B40") ," ",""), _
    TextToDisplay:="Hello"

    Change the anchor cell, address, and texttodisplay as desired...

    You could create a loop to make the text for address or even read a cell in excel (whichever works best for you needs).

    Steve

  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Consider this code that will remove a spaces in values B7 to B40 and concatenate then into your hyperlink
    Code:
    Public Sub Link()
    Hlink = "xxxxxxxx.html?"
    For I = 7 To 40: Hlink = Hlink & Replace(Cells(I, 2), " ", ""): Next I
    ActiveSheet.Hyperlinks.Add Anchor:=[c1], Address:=Hlink, TextToDisplay:="Linked Cell"
    End Sub

  6. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks guys - sorry I haven't got back to you - my email had failed to notify me of replies.

    I'll give those a try, thanks very much

Posting Permissions

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