Microsoft Excel - Hyperlink to open embedded object in Excel

Asked By Nicole Dowd on 07-Jan-11 02:58 AM

Hi,

I'm not very familiar with VBA code so I was hoping there would be an easy way to do this.

I need a hyperlink to open up an embedded object in Excel. Is this an easy thing to accomplish? The problem is, I create these spreadsheets on a regular basis and I'd like to avoid having to go through a long process to link such a thing.

Please let me know if you need me to go into anymore detail. Perhaps I can email you an example of the spreadsheet I'm working with.

Thanks,

Nicole

P.S. I'm using Microsoft Excel 2010

Jackpot . replied to Nicole Dowd on 07-Jan-11 03:51 AM

Hi Nicole

Could you please explain in steps the process you currently follow.

Nicole Dowd replied to Jackpot . on 07-Jan-11 04:02 AM

Hi,

I assume you mean you need me to go into more detail - here goes....

I have a date in Cell J8 and I would like users to be able to click this date and in turn open a PDF file.

I can create a hyperlink to the file but of course all users would need access to the file pathway which none of them do. I can insert an object but that complicates things further for the users as I would have to direct them to the file and then ask them to double click the object to open it.

Perhaps you could provide VBA code for a hyperlink in cell J8 to open object one on sheet 'Attachments'... have I just made any sense then? :) Sorry... I really want to learn VBA code but there's not a lot of user friendly info on the net.

Thanks,

Nicole

Jackpot . replied to Nicole Dowd on 07-Jan-11 04:45 AM

Hi Nicole

In sheet 'Attachments' insert the attachment as below
--Insert>Object>Creat from File>Browse the file>
--Select 'Display as icon' and hit OK
--Suppose the object name is 'Object 1'. (To know this click on the object and refer the name box)

Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to worksheet and try double clicking on cell J8.

If you want this the object to be viewed on cell selection use the below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Address = "$J$8" Then
Application.ScreenUpdating = False
Sheets("Attachments").OLEObjects("Object 1").Verb  
Me.Activate  
Application.ScreenUpdating = True
End If

End Sub

else if you want this the object to be viewed on cell double click try the below.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   
If Target.Address = "$J$8" Then
Application.ScreenUpdating = False
Sheets("Attachments").OLEObjects("Object 1").Verb   
Me.Activate   
Application.ScreenUpdating = True
Cancel = True
End If
End Sub

Nicole Dowd replied to Jackpot . on 07-Jan-11 05:04 AM

That works great. Thanks very much for your help.

James replied to Nicole Dowd on 16-Aug-11 11:14 AM

This worked really well for me too.

How could I adapt the code to work when I click on a autoshape?

Many Thanks

james