VBA Excel Macro - Picture attached to Cell (insert) - Not link

Gstg72 

Refactoring code from Nikolino, you can have some more options, although given we don't know what you wanna do with the pictures later, it will lack some functionality you are looking after (the sort thing...).

Sub insertPicture()
    Dim owsh As Excel.Worksheet
    Dim rng As Excel.Range
    Dim oShp As Excel.Shape
    Dim strFile As String
    
    strFile = Application.GetOpenFilename("Graphic files (*.jpg; *.gif; *.png)," & "*.jpg; *.gif; *.png")
    If strFile <> CStr(False) Then
        Set owsh = ActiveSheet
        With owsh
            On Error Resume Next
            Set rng = Application.InputBox("Select target cell:", "Insert Picture", ActiveCell.Address, Type:=8)
            On Error GoTo 0
            If Not rng Is Nothing Then
                Set oShp = .Shapes.AddShape(msoShapeRectangle, rng(1, 1).Left, rng(1, 1).Top, rng(1, 1).Width, rng(1, 1).Height)
                With oShp
'!!!!! alternative                    
                    .Visible = msoFalse
                    With .Fill 
                        .Visible = msoTrue
                        .UserPicture strFile
                        .TextureTile = msoFalse
                    End With
                    
                    With .TextFrame2.TextRange.Characters
                        With .Font
                            .Size = 11
                            With .Fill
                                .Visible = msoTrue
                                With .ForeColor
                                    .ObjectThemeColor = msoThemeColorLight1
                                    .TintAndShade = 0
                                    .Brightness = 0
                                End With
'!!!!! alternative                    
                                .Transparency = 1 ' ranges({0 = solid} to {1 = hidden})
                                .Solid
                            End With
                        End With
                        With .ParagraphFormat
                            .FirstLineIndent = 0
                            .Alignment = msoAlignLeft
                        End With
                        .Text = strFile
                    End With
'!!!!! alternative                    
                    '.AlternativeText = strFile
                End With
                
'!!!!! alternative                    
                '.Hyperlinks.Add Anchor:=oShp, Address:=strFile
                
                rng(1, 1).FormulaR1C1 = strFile
            End If
        End With
    End If
    
    Set rng = Nothing
    Set oShp = Nothing
    Set owsh = Nothing
End Sub

As you can see, the picture is "inserted" in the workbook, so it can be referred. I have hidden the image inserted

You have some options to continue with the sort issue, as the shapes will be not linked to the cells (so they will not sort):

1. Use the inserted cell as a sorting index
2. Use an inside text as reference (you can hide the text with the transparency value)
3. Use the AlternativeText as reference (commented code)
4. Use the Hyperlink (commented code)
5. ... others to explore

All the alternatives you have are marked with the '!!!!! alternative  tag, so you can choose which one better fits your needings.

Kind regards

Gstg72 

I'm not sure if I understood it correctly from the translation, but I'm still sending you this VBA code with a selection function. If this is not what you had in mind, just ignore it. 

Option Explicit


Sub InsertPicture()
 Dim sPicture As String, pic As Picture

 sPicture = Application.GetOpenFilename _
 ("Pictures (*.gif; *.jpg; *.bmp; *.tif), *.gif; *.jpg; *.bmp; *.tif", _
 , "Select Picture to Import")

 If sPicture = "False" Then Exit Sub

 Set pic = ActiveSheet.Pictures.Insert(sPicture)
 With pic
 .ShapeRange.LockAspectRatio = msoFalse
 .Height = ActiveCell.Height
 .Width = ActiveCell.Width
 .Top = ActiveCell.Top
 .Left = ActiveCell.Left
 .Placement = xlMoveAndSize
 End With

 Set pic = Nothing

 End Sub

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

I found a partial work around ... but it's horrible to think this has to be done. 

You can use the first option to insert the pictures / attached to cells as links, then convert it to a PDF. The PDF can be sent, etc. Then convert it from PDF back to an Excel file. While this seems like a really bad work around, I haven't found any other options yet.