Formula to check: Does cell contain image/picture

[mexmanz]

Hi,

I have a spreadsheet with product data.

Along each row are: item code, description, price, pack etc., but there are also cells which have a picture or image in/on them (these will always be in the same column).

Is there a way to use a formula to confirm which cells within the column contain an image and return true or false argument?

The image is only against products which have been replaced with substitutions and I wish to highlight these.

Many thanks

 

[Damon Ostrander]

Hi Mexmanz,

Here is a function you can use for this:

Function HASpic(Cell As Range) As Boolean

'  Yields TRUE if the cell identified by row and col contains a picture,
'  otherwise FALSE

   Dim Caddress      As String
   Dim Pict          As Object
   
   Application.Volatile
   Caddress = Cell.Address
   
   For Each Pict In Application.Caller.Parent.Pictures
      If Pict.TopLeftCell.Address = Caddress Then
         HASpic = True
         Exit Function
      End If
   Next Pict
   HASpic = False
   
End Function

You would use this to determine if a picture is in cell B4 like this:

=HASpic(B4)

Note that in Excel a picture is technically "in" a cell if the top left corner of the picture is in the cell.

Also I should mention that I took your mention of "picture" seriously. The function will not yield TRUE because some other type of Shape object is in the cell--only if a picture-type Shape object is in the cell.

Keep Excelling.

Damon

 

[mexmanz]

Hi Damon,

Thanks your reply,

Yes from your details I beleive this would work.

Just not sure where I put the code, does it go in Module or Object

I have pasted in both but not working as yet, still learning my way around VBA

many thanks
Andrew

 

[Damon Ostrander]

Hi Andrew,

Just paste the code into a standard macro module. To to this go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste the code into the Code pane.
The function will be immediately available and work just like the built-in worksheet functions.

The HASpic function will only calculate when the worksheet calculates, so if you add or move a picture on the worksheet (which doesn't in itself cause a worksheet recalculation) you will need to press the F9 (recalc) button to cause the HASpic to its result.

I hope this helps.

Damon

 

[glda19]

why dos Damon Ostrander his code only work when b4 or a other one cell is selected.
Why not when there is a range on cells is selected.