CODEWITHSUNDEEP
excelvba
Shubham Agarwal
Shubham Agarwal

Reputation: 11

Hyperlink in excel macro

I want a method to create a hyperlink of some variable in excel macro. My requirement is I have to capture a link in some variable for ex:

abc = InputBox("Enter the path")

now I want to use this abc as a parameter to a hyperlink function and create a hyperlink with name xyz. Can you help me to solve this issue?

Upvotes: 0

Views: 569

Answers (2)

Nelsons Development Solutions
Nelsons Development Solutions

Reputation: 101

OK, here is the code. What it is doing is 1. Asking what URL you want 2. Storing the URL in a variable called URL 3. Going to Sheet 1, adding the hyperlink function to cell A5, linking it to the URL you input 4. Displaying the friendly name you give it in the code

Let me know if you have any questions | you will just need to modify the code for the URL you want or place the links where you want.

Tested and working. Please vote as answer :) 

Sub CreateHyperLink()
Dim URL As String
URL = InputBox("Enter the link")
With Worksheets("Sheet1")
.Hyperlinks.Add Anchor:=.Range("A5"), _
Address:="http://www." & URL, _
TextToDisplay:="Google"
End With
End Sub

Upvotes: 1

nekomatic
nekomatic

Reputation: 6284

Use the HYPERLINK function:

=HYPERLINK(link_location, friendly_name)

Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the Internet.

For example in macro code:

Worksheets("Sheet1").Range("A1").Formula = "=HYPERLINK(""" & link_destination & """,""" & link_text & """)"

Note the use of "" (two double quotes) in the VBA code to produce a single double quote in the cell formula.

Upvotes: 0

Related Questions