We help IT Professionals succeed at work.

How can I create a hyperlink using VBA and Windows navigation?

88 Views
Last Modified: 2017-03-30
I would like to create a hyperlink, say in cell A1, by running a macro that opens a Windows navigation panel.
In principle it would be exactly like right clicking a cell and clicking the hyperlink option.

I would like the macro to specify the cell that the hyperlink would be added to.
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi,

Something like this:
Sub CreateHyperLink()
Dim Ws As Worksheet
Dim Rng As Range
Set Ws = Worksheets("Sheet1")
Set Rng = Ws.Range("A1")

Rng.Parent.Hyperlinks.Add Anchor:=Rng, Address:="", SubAddress:="Sheet1!A5", TextToDisplay:="Click Here to go to A5"

    With Rng.Font
        .ColorIndex = xlAutomatic
        .Underline = xlUnderlineStyleNone
    End With

    With Rng.Characters(Start:=21, Length:=2).Font
        .Underline = xlUnderlineStyleSingle
        .Color = -4165632
    End With

End Sub

Open in new window

It will create hyperlink in Cell A1 of Sheet1 to go to Cell A5
Create-Hyper-Link.xlsm
spar-kleOperations Director

Author

Commented:
Thanks Shuns.  I can see that will add a hyperlink according to the hard coding. However I want to click on a button ...open a navigation pane ...navigate to a file click on the file and create a link in a cell designated by the code.
spar-kleOperations Director

Author

Commented:
Sorry "Shums"
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
spar-kleOperations Director

Author

Commented:
That's excellent.
Thanks Shums
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome! Please to help