Hyperlinks in Text Boxes

bruntonomo

New Member
Joined
Jul 29, 2018
Messages
46
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
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
Code:
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 AppleSheet1!A1
2
2 PearSheet2!A1
3
3 BananaSheet3!A1
4
5
6
Textbox
(on other sheet)
contains:
7
1 Apple
2 Pear
3 Banana
Sheet: Links
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
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
Code:
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
Code:
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
 
Last edited:

bruntonomo

New Member
Joined
Jul 29, 2018
Messages
46
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
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
 
Last edited:

bruntonomo

New Member
Joined
Jul 29, 2018
Messages
46
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
I cannot see your screenshots
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,118
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top