Results 1 to 5 of 5

Thread: Clicking different cells to open files
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clicking different cells to open files

    Hi guys. I have a spread sheet with multiple options on there. Previously I had used hyperlinks to link to word documents for the shipping labels but people are clicking by mistake a making a right hash of things so I am now looking at adding VBA code where you would double click a set cell and the appropriate word file will open. I have set up double click events before but never for this purpose.

    Any input would be great.

    Regards

    Dan
    Last edited by Fluff; Nov 21st, 2018 at 08:41 AM.

  2. #2
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,680
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Clicking different cells to open files

    Whilst it is easy to write code for the double-click event to follow the hyperlink - it does not negate the fact that the user has clicked the hyperlink (and that click would need to be cancelled). Now it seems that there is no Cancel option for FollowHyperlink event. Therefore I suggest this approach:

    Adapted from here: https://www.ozgrid.com/forum/forum/h...ntil-confirmed

    Rather than have the hyperlink address point to the file, have the link point back to the parent cell (i.e. itself). Then you can use the ScreenTip to hold the actual destination address. The following code will follow the address in the ScreenTip. This code belongs in the relevant sheet module.
    Code:
    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Dim strAddress          As String
        
        Application.EnableEvents = False
        
        strAddress = Target.Address
    
    
        If CBool(Len(strAddress)) Then
            If MsgBox(Prompt:="Follow hyperlink?", Buttons:=vbYesNo + vbQuestion, Title:="Hyperlink Activated") = vbYes Then
                Call ThisWorkbook.FollowHyperlink(Address:=strAddress)
            End If
        End If
        
        Application.EnableEvents = True
    End Sub
    Last edited by Jon von der Heyden; Nov 21st, 2018 at 09:33 AM.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clicking different cells to open files

    I had originally thought of using something like this

    Sub Open_Word_Document() 'Open an existing Word Document from Excel Dim objWord As Object Set objWord = CreateObject("Word.Application") objWord.Visible = True 'Change the directory path and file name to the location 'of the document you want to open from Excel objWord.Documents.Open "C:\Documents\myfile.doc" End Sub

    But this seems to target the whole spread sheet rather than a specific cell. If this can be made to target a specific cell then I can get this to work

  4. #4
    New Member
    Join Date
    Nov 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clicking different cells to open files

    Quote Originally Posted by Jon von der Heyden View Post
    Whilst it is easy to write code for the double-click event to follow the hyperlink - it does not negate the fact that the user has clicked the hyperlink (and that click would need to be cancelled). Now it seems that there is no Cancel option for FollowHyperlink event. Therefore I suggest this approach:

    Adapted from here: https://www.ozgrid.com/forum/forum/h...ntil-confirmed

    Rather than have the hyperlink address point to the file, have the link point back to the parent cell (i.e. itself). Then you can use the ScreenTip to hold the actual destination address. The following code will follow the address in the ScreenTip. This code belongs in the relevant sheet module.
    Code:
    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Dim strAddress          As String
        
        Application.EnableEvents = False
        
        strAddress = Target.Address
    
    
        If CBool(Len(strAddress)) Then
            If MsgBox(Prompt:="Follow hyperlink?", Buttons:=vbYesNo + vbQuestion, Title:="Hyperlink Activated") = vbYes Then
                Call ThisWorkbook.FollowHyperlink(Address:=strAddress)
            End If
        End If
        
        Application.EnableEvents = True
    End Sub
    I will give this a try. But there might be an issue because I have a double click event set up to expand/unhide hidden rows below.

  5. #5
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,680
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Clicking different cells to open files

    Quote Originally Posted by danielpalfrey View Post
    I will give this a try. But there might be an issue because I have a double click event set up to expand/unhide hidden rows below.
    You can still have a double click event.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •