Excel 2007 VBA to automate HYPERLINK function - PLEASE HELP

By prizerebel2007 ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Inserting formulas using VBA

by RobRobason In reply to Excel 2007 VBA to automat ...

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.

Related Discussions

Related Forums