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.

 

[Yongle]

There is no obvious way to do what you want. But it I will be following your thread with interest

Not what you requested but may be a useful start for someone
- insert multiple links into a singe Excel cell
https://excel.tips.net/T002805_Putting_More_than_One_Hyperlink_in_a_Cell.html

 

[Yongle]

This is very close to what you want - click on textbox \ select a line number \ hyperlink is followed

To test

- insert NEW sheet with name "Links"
- in col A : friendly names prefixed by row number
- in col B: equivalent valid hyperlinks (see simple example below)
- do not use a header row (keeps things simple)

- on another sheet
- insert a simple textbox
- friendly names (as in col A above) are separate lines in textbox

The code
- assign macro to textbox

Sub TextBox1_Click()
    Dim shp As Shape, txt As String, LineNo As Long
    Set shp = ActiveSheet.Shapes(Application.Caller)
    txt = shp.TextFrame2.TextRange.Characters.Text
    LineNo = Application.InputBox("which line?", "Get text", 1, , , , , 1)
    Sheets("Links").Cells(LineNo, "B").Hyperlinks(1).Follow
End Sub

Notes
- this could be automated to make the list in the textbox autofill from values in column A etc

Excel 2016 (Windows) 32 bit

	A​	B​
1​	1 Apple	Sheet1!A1
2​	2 Pear	Sheet2!A1
3​	3 Banana	Sheet3!A1
4​
5​
6​	Textbox (on other sheet) contains:
7​	1 Apple 2 Pear 3 Banana

Sheet: Links

 

[Yongle]

Have you considered using a simple combobox (also known as DropDowns) ?
- select an item from the dropdown to follow the hyperlink

Add combobox like this
Developer tab \ Insert \ ComboBox from Form Controls (not active-x)

The code
- assumes the same layout in sheet Links as in previous example

the item number in the dropdown is the same as the row number in sheet Links

Sub DropDown1_Change()
    With ActiveSheet.Shapes("Drop Down 1").ControlFormat
        Sheets("Links").Cells(.Value, "B").Hyperlinks(1).Follow
    End With
End Sub

code to refresh the dropdown is simple too

Sub Refresh_DropDown()
    Dim Links As Worksheet, Cel As Range:    Set Links = Sheets("Links")
    With ActiveSheet.Shapes("Drop Down 1").ControlFormat
        .RemoveAllItems
        For Each Cel In Links.Range("A1", Links.Cells(Rows.Count, "A").End(xlUp))
            .AddItem Cel
        Next
    End With
End Sub

To test
- insert the combobox
- add the VBA
- place values in sheet Lists
- refresh dropdown
- select a value from dropdown

 

[bruntonomo]

Yongle, thank you so much for posting three responses. I really do appreciate your willingness to help. The drop down menus is actually already part of the plan on my project. They will be residing inside frozen panes at the top of the document for a fixed navigational header. However, these drop down menus will link to broader subject matters. Links in the main part of the document will link to more specific instances within those broader subjects. That's why I need both.

If I can hold off on a ton of programming, that would be fantastic. I guess I'm not sure why Excel cannot understand something as simple as hyperlinks in a text box. That seems to be a very serious oversight by Microsoft.

 

[Yongle]

The drop down menus is actually already part of the plan on my project. They will be residing inside frozen panes at the top of the document for a fixed navigational header. However, these drop down menus will link to broader subject matters. Links in the main part of the document will link to more specific instances within those broader subjects. That's why I need both.

I do not understand why you think this
- why can't you use dropdowns for both?

I presume that you understand that I am NOT referring to cell dropdowns

 

[bruntonomo]

A drop down menu in the body of the document won't work for what I'm trying to do. I apologize for not describing my project better in the original post. I have a ton of text because the project is an operating procedures manual. That's why I have text boxes. Simple links work best as the links are included within the context of what I'm writing. That's why a drop down menu won't really work. I would use Word, but they don't have frozen panes (fixed header). So I'm trying to make do with Excel.

 

[Yongle]

Does this describe what you want ?

A single textbox with multiple hyperlinks where any word or phrase contained in the textbox could be a hyperlink

 

[bruntonomo]

Below are screenshots of the layout I have created.

Section Menu

Body text

I used the text box tool to create the spaces where the text is.

 

[Yongle]

I cannot see your screenshots