Hyperlinks in Text Boxes

[bruntonomo]

Is there a way to place multiple hyperlinks in a single text box? Every time I select some text to make into a hyperlink, it makes the entire text box the link. I have a long Excel workbook comprised of long text boxes for ease of alignment purposes. I'm using Excel instead of Word because Word does not have a freeze frames function. I need the freeze frames for navigation of the document.

I know that I could make a million text boxes with hyperlink text to get around this, but that's seems super sloppy and majorly inconvenient. Any help would be greatly appreciated.

 

[bruntonomo]

Sorry, I submitted without adding the links. I edited the post. Please try again.

 

[Yongle]

The only thing I can think of is to do this which is simple and it works (I have tested it)

Use a separate text box for each link
- assign the SAME macro (see below) to all textboxes that need same treatment
- hide the lines around the textboxes if you wish
- group the textboxes together

In sheet LINKS (as in the very early example I provided)
Column A = textbox text
Column B = hyperlink

The VBA to be assigned to every textbox

Sub Any_TextBox_Click()
    Dim shp As Shape, txt As String
    Set shp = ActiveSheet.Shapes(Application.Caller)
    txt = shp.TextFrame2.TextRange.Characters.Text
    On Error Resume Next
    Sheets("Links").Range("A:A").Find(txt, LookIn:=xlValues, lookat:=xlWhole).Offset(, 1).Hyperlinks(1).Follow
    If Err.Number > 0 Then MsgBox "Link Failed"
End Sub

What it does
- clicking on a textbox allows VBA to identify the correct textbox
- VBA then looks up the value contained in that textbox
- the value in the textbox is looked up in (sheet "Links") column A
- if found , the hyperlink in column B is actioned

 

[Yongle]

If I can hold off on a ton of programming, that would be fantasti

- your wish is granted
- ONE tiny macro


Ignore post#12 - this is simpler



Use a separate text box for each link
- textbox text can be any text (it is not used)
- assign the SAME macro (see below) to all textboxes that need same treatment
- hide the lines around each textbox if you wish
- group the textboxes together

In sheet named "Links"
Column A = a list of all textbox names
Column B = hyperlinks

The VBA to be assigned to every textbox

Sub Any_TextBox_Click()
    On Error Resume Next
    Sheets("Links").Range("A:A").Find(Application.Caller, LookIn:=xlValues, lookat:=xlWhole).Offset(, 1).Hyperlinks(1).Follow
    If Err.Number > 0 Then MsgBox "Link Failed"
End Sub

What it does
- clicking on a textbox allows VBA to identify the correct textbox
- VBA then looks for the name of the textbox in column A
- if found, the hyperlink in column B is actioned


Note that there is a space in default textbox names

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]TextBox 1[/td][td]Sheet1!A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]TextBox 2[/td][td]Sheet2!A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]TextBox 3[/td][td]Sheet3!A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Links[/td][/tr][/table]

 

[bruntonomo]

That's pretty cool! I was able to get it to work after some tinkering. However, if I have to create a text box for each link, I could probably skip the VBA all together and just use a simple hyperlink on each text box. That's what I was trying to avoid doing. If there was some way to force Excel to see text as its own entity within a text box, that would be fantastic. Your script is EXACTLY what I was thinking may be the solution after seeing your early post. Problem seems to be that we can't get around Excel forcing a text box into being a single object that can only do one thing (all or nothing kind of thing). It doesn't seem to understand that the text within it can have its own individual values. What I don't understand is that Word can do this, but Excel seems to refuse to do it.

I really appreciate all the time and energy you have put into helping me try to figure this out.

 

[Yongle]

Are you sure you have backed the correct horse?
- you have rejected Word, but are creating a manual. Word is the better tool for that and you said Word can do exactly what you want
- Excel is good at manipulating and analysing numbers but a lot of the other "frilly" stuff is a bit of a clumsy bolt-on

Perhaps someone else will come along and offer you something closer to what you want with respect to the textbox. Good luck

 

[bruntonomo]

After thinking this over yesterday, I am going back to Word. When I initially started the project, I wanted to do something web based for ease of use and finding specific information quickly. However, the company I'm working for told me that was not an option. So I tried to keep with the same idea, but convey it with Office products. I wanted to work with Excel because its freeze frames would be able to replicate a fixed web page header for general navigation purposes. However, I've found that there are serious usability issues with it that cannot be overcome easily and would really hinder practicality of the end product. So I will go back to Word. It won't be quite as slick and easy to use, but it will do the essentials well. Just sad that Microsoft has such a glaring issue with something so simple like text boxes.

Again, thank you for your help!

 

[Yongle]

Hope it goes smoothly!

 

[WeaselJ]

Is there a way to place multiple hyperlinks in a single text box? Every time I select some text to make into a hyperlink, it makes the entire text box the link. I have a long Excel workbook comprised of long text boxes for ease of alignment purposes. I'm using Excel instead of Word because Word does not have a freeze frames function. I need the freeze frames for navigation of the document.

I know that I could make a million text boxes with hyperlink text to get around this, but that's seems super sloppy and majorly inconvenient. Any help would be greatly appreciated.

The best way to solve this is to create the hyperlink text in another MS application such as Word, then copy/paste the hyperlink text into your textbox.