Attaching file to hyperlink

ryan_law2000

Well-known Member
Joined
Oct 2, 2007
Messages
738
Here is what i want to do
When you click on Cell C5 (which will say "ADD") I would Like the (Computer Browse Menu) to appear
you can then search for the file and press Attach
This will then create a hyperlink in that cell (or in a different cell) Saying "File Attached"
when you click on "File Attached" it will then open that file

I completly lost on how to do this
but here is the formula i have so far when it comes to click on the cell


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrValues
If Target.Count > 1 Then Exit Sub
If Intersect(Range("C5:C5000"), Target) Is Nothing Then Exit Sub


End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I have found this code that seems to be working almost how i want it to:
First Question:
Instead of it displaying the file name when attahced can I just make it say "FILE ATTACHED"
Sencond Question:
I have another (Private Sub Worksheet_SelectionChange) right above this one... How can I make them both work, I either need to change the name(which hassent worked for me) or add it to this private sub (which I always get "End If" Errors

PLEASE HELP lol

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    
    Dim r As Range
Dim Fname As String
Dim response
 If Target.Count > 1 Then Exit Sub
    If Intersect(Range("B5:B5000"), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
For Each r In Target
    If Trim(Len(r.Value)) = 1 Then r.Value = UCase(r.Value)
Next
    response = MsgBox("Would You Like To Attach a File?", vbYesNo)
    If response = vbYes Then
        Fname = Application.GetOpenFilename
        ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), Address:=Fname
    End If
Application.EnableEvents = True
End Sub
 
Last edited by a moderator:
Upvote 0
Hi,

It arrives also on the Excelversion.

Code:
ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), _
    Address:=Fname, TextToDisplay:="FILE ATTACHED"
You can use only once Worksheet_Change, or Worksheet_SelectionChange.

Case_Germany
 
Upvote 0
here are the 2 codes, how can i get both to work?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrValues
    If Target.Count > 1 Then Exit Sub
    If Intersect(Range("A5:A5000"), Target) Is Nothing Then Exit Sub
    
    arrValues = Target.Offset(, 1).Resize(, 3)
    
    arrValues = Application.Transpose(arrValues)
    
    arrValues = Application.Transpose(arrValues)
    UserForm14.TextBox5 = Join(arrValues, " - ")
    
    UserForm14.Show
    
    
    End Sub
    

        Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    
    
    Dim r As Range
Dim Fname As String
Dim response
 If Target.Count > 1 Then Exit Sub
    If Intersect(Range("B5:B5000"), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
For Each r In Target
    If Trim(Len(r.Value)) = 1 Then r.Value = UCase(r.Value)
Next
    response = MsgBox("Would You Like To Attach a File?", vbYesNo)
    If response = vbYes Then
        Fname = Application.GetOpenFilename
        ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), Address:=Fname
    End If
Application.EnableEvents = True
End Sub
 
Upvote 0
Thank you... the "File Attached" worked perfect
so if i can only have one "Private Sub Worksheet_SelectionChange"
how can I fit my 2 together?
 
Upvote 0
Hi again,

try...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim arrValues
    Dim Fname As String
    Dim response
    On Error GoTo Fin
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Range("A5:A5000"), Target) Is Nothing Then
        arrValues = Target.Offset(, 1).Resize(, 3)
        arrValues = Application.Transpose(arrValues)
        arrValues = Application.Transpose(arrValues)
        UserForm14.TextBox5 = Join(arrValues, " - ")
        UserForm14.Show
    Else
        If Not Intersect(Range("B5:B5000"), Target) Is Nothing Then
            response = MsgBox("Would You Like To Attach a File?", vbYesNo)
            If response = vbYes Then
                Fname = Application.GetOpenFilename
                ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), _
                    Address:=Fname, TextToDisplay:="FILE ATTACHED"
            End If
        End If
    End If
Fin:
    Application.EnableEvents = True
End Sub
Case_Germany
 
Upvote 0
So In this formula how can I make it so when:
cell B5:B5000 = "FILE ATTACHED" then it will not run the
"Fname = Application.GetOpenFilename"

what is happening now is when i try and open the attched file it runs the "attach file" again

So i need to be able to click on the hyperlink created and it just open the file (NOT run the "Fname = Application.GetOpenFilename")

Code:
If Not Intersect(Range("B5:B5000"), Target) Is Nothing Then
            response = MsgBox("Would You Like To Attach a File?", vbYesNo)
            If response = vbYes Then
                Fname = Application.GetOpenFilename
                ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), _
                    Address:=Fname, TextToDisplay:="FILE ATTACHED"


ANY Ideas?
 
Upvote 0
Hi again,

ANY Ideas?

Yes. :biggrin:


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim arrValues
    Dim Fname As String
    Dim response
    On Error GoTo Fin
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Range("A5:A5000"), Target) Is Nothing Then
        arrValues = Target.Offset(, 1).Resize(, 3)
        arrValues = Application.Transpose(arrValues)
        arrValues = Application.Transpose(arrValues)
        UserForm14.TextBox5 = Join(arrValues, " - ")
        UserForm14.Show
    Else
        If Not Intersect(Range("B5:B5000"), Target) Is Nothing Then
            If Not Target.Hyperlinks.Count = 1 Then
                response = MsgBox("Would You Like To Attach a File?", vbYesNo)
                If response = vbYes Then
                    Fname = Application.GetOpenFilename
                    ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), _
                        Address:=Fname, TextToDisplay:="FILE ATTACHED"
                End If
            End If
        End If
    End If
Fin:
    Application.EnableEvents = True
End Sub
Case_Germany
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top