Software·43,882 discussions

Excel 2007 VBA to automate HYPERLINK function – PLEASE HELP

Locked

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.

Topic

Clarifications

In reply to Excel 2007 VBA to automate HYPERLINK function – PLEASE HELP

Clarifications

Inserting formulas using VBA

In reply to Excel 2007 VBA to automate HYPERLINK function – PLEASE HELP

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.

[Author]

Replies