embed an object in excel based on user values

[learning_grexcel]

Hi,

I have an excel file with embedded pdf object (as an icon) in B1.

Is it possible to change the file based on the user values entered in A1 ?


For e.g. if user types the name "tony" in A1, it shall show the resume of that person in B1(embedded icon in pdf). If they type again "Greg", it shall automatically replace the previous pdf and show Greg's pdf file there (as an icon)

 

[learning_grexcel]

I would suggest that you record a macro while you manually insert your PDF file onto your worksheet. In the dialog box, when you select "Display as Icon", the PDF icon should be automatically displayed. If so, the recorded code should contain the proper icon filename.

Does this help?

Yes thanks a lot.

Here is my final codes

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 And (Target.Row >= 1 And Target.Row <= 20) Then Exit Sub
    Dim objOLE As OLEObject
    Dim strFolder As String
    Application.EnableEvents = False
    For Each objOLE In Me.OLEObjects
        If objOLE.TopLeftCell.Address = Target.Offset(, 4).Address Then
            objOLE.Delete
        End If
    Next objOLE
    If Len(Target) > 0 Then
        strFolder = "C:\**\**\Desktop\" 'change the path to your folder accordingly
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        On Error Resume Next
        Me.OLEObjects.Add _
            Filename:=strFolder & Target.Value & ".pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
            IconFileName:="C:\Program Files (x86)\Adobe\Acrobat 10.0\Acrobat\Acrobat.exe", _
            IconIndex:=0, _
            IconLabel:=Target.Value, _
            Left:=Target.Offset(, 4).Left, _
            Top:=Target.Offset(, 4).Top
        On Error GoTo 0
    End If
    Application.EnableEvents = True
End Sub

This is working fine as per the initial requirement. However, now I have one more requirement as below.

I have A1 fixed values as below. I want the icon to be inserted based on B1 values also which are drop-down list.
The icon file names match with A1 values. However, whenever the user select the B1 value from drop-down list, it should pickup the icon from different folder based on the location. For e.g. In desktop I have one folder "Head office", it shall pick up the pdf file from this folder if the user select B1 value "Head office". If the user select "branch office", it should insert the pdf icon from "Branch office" folder in desktop. Please note file names are same but only folders are different, which shall be selected based on the B1 value. Is it possible? Again thanks.

XYZ
ABC
TYN
etc. upto 20 values.

B1 values drop-down list
Location:Head Office
Location:Branch Office
Location:Site office