-
January 24th, 2013, 08:33 AM
#1
Add Hyperlink to cell
I have the following code that works fine but I need to modify it to do two things but for two separate files.
What I need is explained in the attachments:
The code is at follows:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'change "c:\tmp\" to whatever reference you need
'a cell, a public variable, a fixed string
If Target.Column = 7 Then
MakeHyperLink Target, "H:\Darlene.Sippio\Personel\Test Department"
End If
End Sub
Code:
Public Function MakeHyperLink(InRange As Range, _
ToFolder As String, _
Optional InSheet As Worksheet, _
Optional WithExt As String = "doc") As String
Dim rng As Range
Dim Filename As String
Dim Ext As String
'check to see if folder has trailing \
If Right(ToFolder, 1) <> "\" Then
Filename = ToFolder & "\"
Else
Filename = ToFolder
End If
'check to see if need ext
If WithExt <> "" Then
'check to see if ext has leading dot
If Left(WithExt, 1) <> "." Then
WithExt = "." & WithExt
End If
End If
'if not explicit sheet then assign active
If InSheet Is Nothing Then
Set InSheet = ActiveSheet
End If
'now for every cell in range
For Each rng In InRange
'does range have value
If rng <> "" Then
'make hyperlink to file
InSheet.Hyperlinks.Add Anchor:=rng, Address:= _
Filename & rng.Text & WithExt, TextToDisplay:=rng.Text
End If
Next
End Function
I need help as soon as I can get it so if anyone has any ideas please let me know.
-
January 24th, 2013, 12:05 PM
#2
Re: Add Hyperlink to cell
If it's your site, that's one thing. If it's on the web, then I don't think Excel is the best tool for the job.
-
January 24th, 2013, 12:09 PM
#3
Re: Add Hyperlink to cell
It's not a website or anything. It's an excel file. I want to add files not urls.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|