CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Question 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:

    Name:  Test Log.jpg
Views: 947
Size:  84.8 KB

    Name:  CCB.jpg
Views: 899
Size:  97.7 KB

    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.

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Sep 2012
    Posts
    2

    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
  •  





Click Here to Expand Forum to Full Width

Featured