Software

Ask something
Question Locked

Excel 2007 VBA to automate HYPERLINK function - PLEASE HELP

0 helpful
2 replies

by prizerebel2007

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have looked around and cannot find a good answer to my question. I need VBA code to create a hyperlink to the selected cell in the selected cell with the current text. I know this is confusing so here's an example. Cell A3 has text I-040603. I want to be able to select the cell and run a macro to create =HYPERLINK("\\sbssvr\dispatch\Excel Templates\Calendar.xltm#Sunday!A3","I-040603")
for that cell. I use the HYPERLINK function opposed to the other hyperlink way b/c the main purpose of the hyperlink is so that i can copy the cell to a new workbook and still have the link work.

2 replies

by Member 22177997

Use the Range.Formula property to insert your hyperlink. See the example below:

Sub EnterFormula()
Dim myFormula As String
myFormula = "=HYPERLINK(" & Chr(34) _
& "\\sbssvr\dispatch\Excel Templates\Calendar.xltm#Sunday!A3" _
& Chr(34) & "," & Chr(34) & "I-040603" & Chr(34) & ")"
Worksheets("Sheet1").Range("D6").Formula = myFormula
End Sub

Chr(34) is the double-quote character.

This discussion is closed to new replies.