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

spar-kle
spar-kle used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
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"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

Try below:
Option Explicit
Sub CreateHyperLink()
Dim Ws As Worksheet
Dim Rng As Range
Dim fName As String
Set Ws = Worksheets("Sheet1")
Set Rng = Ws.Range("A1")
fName = Application.GetOpenFilename
Rng.Value = fName
Rng.Hyperlinks.Add Anchor:=Rng, Address:=Rng.Value, TextToDisplay:="Open File"
End Sub

Open in new window

Create-Hyper-Link_v1.xlsm
spar-kleOperations Director

Author

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

Commented:
You're Welcome! Please to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial