Add a hyperlink to a cell

[LogicalHammer1973]

Hi,

Can someone help, i'm relatively new at VBA coding and have got stuck.

Im trying to add a hyperlink to a PDF file created in the VBA macro to a cell in row F (tried sh.range ("F" & n +1).value = hyperlink etc, but no joy ), I've got the code as follows, but cant seem to workout how to create the hyperlink? I would like to name the hyperlink after the file name created for the PDF.

extract of working code so far:

Sheets("Near Miss").Range("A1:N47").ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"c:\my documents\" & ComboBox1.Value & "_Near Miss_" & Format(Now, "dd_mm_YY_hh_mm") & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Workbooks.Open "C:\Users\mulhalla\Documents\KC Site Near Miss Reports.xlsm"
Set sh = Workbooks("KC Site Near Miss Reports").Sheets("Near-Miss")
n = sh.Range("B" & Application.Rows.Count).End(xlUp).Row
sh.Range("B" & n + 1).Value = Me.ComboBox1.Value
sh.Range("C" & n + 1).Value = Me.TextBox1.Value
sh.Range("D" & n + 1).Value = Me.TextBox2.Value
sh.Range("E" & n + 1).Value = Me.TextBox3.Value
sh.Range("H" & n + 1).Value = "No"
Workbooks("KC Site Near Miss Reports").Close SaveChanges:=True

If anyone can help that would be much appreciated,

PS Sorry if coding isn't great.

 

[COwen]

Just add a line like this...

Sheet1.Range("A1").Hyperlinks.Add Address:="C:\Documents\mypath.extension", TextToDisplay:="The Name You Want"

 

[COwen]

sh.range ("F" & n +1) .Hyperlinks.Add Address:="C:\Documents\mypath.extension", TextToDisplay:="The Name You Want"

In your case.

 

[LogicalHammer1973]

Hi COwen,

Many thanks for the replay, I've tried as you suggested but get an error 450 (wrong number of arguments or invalid property assignment) with the modified code as below:

Worksheets("Near Miss").Visible = True
Sheets("Near Miss").Range("A1:N47").ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"c:\my documents\" & ComboBox1.Value & "_Near Miss_" & Format(Now, "dd_mm_YY_hh_mm") & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
pdfile = "c:\my documents\" & ComboBox1.Value & "_Near Miss_" & Format(Now, "dd_mm_YY_hh_mm") & ".pdf"
Worksheets("Near Miss").Visible = False
Workbooks.Open "C:\Users\mulhalla\Documents\KC Site Near Miss Reports.xlsm"
Set sh = Workbooks("KC Site Near Miss Reports").Sheets("Near-Miss")
n = sh.Range("B" & Application.Rows.Count).End(xlUp).Row
sh.Range("B" & n + 1).Value = Me.ComboBox1.Value
sh.Range("C" & n + 1).Value = Me.TextBox1.Value
sh.Range("D" & n + 1).Value = Me.TextBox2.Value
sh.Range("E" & n + 1).Value = Me.TextBox3.Value
sh.Range("F" & n + 1).Hyperlinks.Add Address:=(pdfile), TextToDisplay:="Link"
sh.Range("H" & n + 1).Value = "No"

When i hover over the pdfile in address is pointing to the file created.

Any ideas?

 

[COwen]

Where is it throwing the error?

 

[LogicalHammer1973]

Where is it throwing the error?

Its generating error 450 (wrong number of arguments or invalid property assignment)

 

[Fluff]

Try

With sh.Range("F" & N + 1)
   .Hyperlinks.Add .Offset(0, 0), pdfile, "", , "Link"
End With

 

[LogicalHammer1973]

Fluff,

Try

With sh.Range("F" & N + 1)
   .Hyperlinks.Add .Offset(0, 0), pdfile, "", , "Link"
End With

Fluff, that worked thank you both for your help on this one.

 

[Fluff]

Glad we could help & thanks for the feedback