add hyperlink to a cell

**qzlvyh0524** · 02-21-2018, 04:09 PM

Hello everyone,

I am trying to add hyperlink in a cell, it works find :

ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE"")"

But when I am trying to change the "CLICK HERE" ,I want to add variables in it, it doesn't work this time. please advise:

Dim mySheetName As String

mySheetName = "sheet12"

ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE to link to : " & mySheetName ")"

Thanks a lot!

**kev_** · 02-21-2018, 05:17 PM

try

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

although I would expect the sheet name in the friendy name to match what is in the hyperlink
so..

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

**qzlvyh0524** · 02-22-2018, 06:22 PM

This part is magic:

""" & mystr & """

if I want to use the variable in the hyperlink, I have to add "&"s and these quotes to surround it.

it works great but I don't know why.

Thanks!

**kev_** · 02-23-2018, 03:59 AM

The "magic" .....

Please Login or Register  to view this content.

could also be written out like this:

Please Login or Register  to view this content.

to return this formula:
=HYPERLINK( " [Workbook.xlsx]sheet12!A1 " , " CLICK HERE to link to : sheet12 " )

Excel requires the 4 quote marks in the derived formula
- but VBA requires the formula string itself to be inside quote marks
- so we need to place quote marks inside quote marks

Quote marks can be inserted inside quote marks using 2 methods:
- either double up the quote marks
- or use chr(34) which is the character code for a quote mark

add hyperlink to a cell

LinkBack

Thread Tools

Rate This Thread

Display

add hyperlink to a cell

Re: add hyperlink to a cell

Re: add hyperlink to a cell

Re: add hyperlink to a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] With Hyperlink formula & vba, I want to copy text of hyperlink to the cell it's linked to

Using a hyperlink to select a cell and instantly activate that cell's hyperlink

Is it possible to have a hyperlink & a 'rollover' hyperlink in the same cell????

Can you add a hyperlink to a cell that already contains a rollover hyperlink?

[SOLVED] Excel 2010 Create a macro to check if cell contains hyperlink then apply hyperlink style

[SOLVED] How do I create a hyperlink to a cell with the hyperlink function

script to hyperlink and reference a cell value in the hyperlink

Bookmarks

Bookmarks

Posting Permissions