if image is present, adjust autoshape

**Armitage2k** · 09-04-2009, 12:55 PM

Hi there.

question from a noob please. How can i let excel check if there is a picture in in a cell of a range in column A, and if yes, select the autoshape in the row cell but column B on sheet2, and adjust its interior color to white?

Would appreciate if someone could advise me some code

thanks,
A2k

**Andy Pope** · 09-04-2009, 01:00 PM

You still need to be using the TopLeftCell property in order to determine a shapes position relative to the worksheet.

Maybe things would be clearer if you posted small example file

**Armitage2k** · 09-04-2009, 01:20 PM

here a quick example. the macro should check if there is a picture in column B on sheet "Picture", and if yes, adjust the shape in the same row but column L on sheet "VIP" to white.

thanks!
A2k

**Armitage2k** · 09-05-2009, 05:24 AM

no ideas anyone?

**blackworx** · 09-05-2009, 06:11 AM

try this

Please Login or Register  to view this content.

**Andy Pope** · 09-05-2009, 07:12 AM

This takes a different approach using webdings2 font rather than images.

The user defined function returns true or false if the picture is there on sheet picture.

Please Login or Register  to view this content.

The formula would be

=IF(UDF_HASPICTURE("Picture","B" & ROW()),"é","")

**Armitage2k** · 09-05-2009, 07:25 AM

@ blackworks

thank you very much for this, after making some minor adjustments to your code, it works flawlessly now. This helps me to finalize a project I was torturing myself with for quite a while already. thanks a lot!

thanks to all the other contributors as well,
A2k

**blackworx** · 09-05-2009, 08:30 AM

You're welcome A2k, although Andy's solution is more robust imo. My code is a quick and dirty fix, depends on a lot of assumptions and is therefore quite "brittle". Good luck with the rest of your project