There are several posts here, and elsewhere that suppose to answer the question, "How do you get the name of a picture placed in a cell by Excel's new 'Place in Cell' feature?" This apparently differs from the "Picture.Insert(path)" method.
Both of the methods suggested (as shown in the following code) reveal nothing. There may be minor errors in them, but they have be changed many times to try to a picture's NAME or EXISTENCE.
Apparently this code may work when the Picture is placed OVER the cell. However, each time this happens the name changes to something like "Picture ##". I would like to count all of the inserted images AND get a static name (or at least evidence of it's presence in a cell.)
FYI, the worksheet is Unprotected when running the code.
Public Function GetPictureName() As String
' Return the name of the Picture in the current cell
Dim shp As Shape
GetPictureName = vbNullString
For Each shp In ActiveSheet.Shapes
If shp.TopLeftCell.address = ActiveCell.address Then
GetPictureName = shp.Name
End If
Next
End Function
Public Function Is_PictureInCell() As Boolean
' Is there a picture in the current cell
Dim pic As Picture
Is_PictureInCell = False
For Each pic In Sheets(1).Pictures
If pic.TopLeftCell.address = ActiveCell.address Then Is_PictureInCell = True
If InStr(1, pic.Name, "Picture") > 0 Then Debug.Print pic.Name
Next
End Function
Both of these functions have been changed to look for "Pictures" and "Shapes" with no success when the Picture is in the cell. Although I could use the "old" insert method I am trying to future-proof my code by using Office 365 features.
It appears that there isn't an in-cell picture object in the Excel object model yet. It is not part of the Shapes or Pictures collections, and I'm not sure if it even has a name like a regular shape object.
Here is a workaround script to detect in-cell picture in a cell. It may not be perfect, but hopefully, it offers some useful ideas.
Sub Demo()
Dim c As Range
Set c = Range("A1")
Debug.Print IIf(HasPicInCell(c), "Found", "No") & " pic-in-cell in " & c.Address
End Sub
' Argument rCell is a single cell, modify as needed
Function HasPicInCell(rCell As Range) As Boolean
Dim ShpCnt As Long, Sht As Worksheet
Set Sht = rCell.Parent
ShpCnt = Sht.Shapes.Count
' pic in cell >> pic over cells, it is the last shape object
rCell.PlacePictureOverCells
If Sht.Shapes.Count = 0 Then
HasPicInCell = False
Else
HasPicInCell = (ShpCnt < Sht.Shapes.Count)
' get the last shape
Dim Shp: Set Shp = Sht.Shapes(Sht.Shapes.Count)
' Excel 365 [Version 2411 (Build 18210.20000)] crashes without the following line.
' I'm not sure if this is a bug in the version or an issue with my PC.
ActiveCell.Select
' ***
Shp.Select
' pic over cells >> pic in cell
Shp.PlacePictureInCell
End If
End Function
If Alt Text
of pic-in-cell is not used in your file, the function may be simplified as below.
' Argument rCell is a single cell, modify as needed
Function HasPicInCell(rCell As Range) As Boolean
On Error Resume Next
rCell.UpdatePictureInCellAlternativeText ""
HasPicInCell = (Err.Number = 0)
On Error GoTo 0
End Function