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

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.

spar-kleOperations DirectorAsked: 2017-03-29

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-03-29

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 DirectorAuthor Commented: 2017-03-29

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 DirectorAuthor Commented: 2017-03-29

Sorry "Shums"

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-03-29

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial