conditionally formatting a picture on Excel Sheet

[dasiga444]

I am using Excel 2007.
I am having a picture on the column D of my Excel Sheet.

However this picture should be visible only if the cells A1 to A10 are all filled with values.
If there are no values in any of the cells, the picture should not appear.
If all the values from A1 to A10 are there, then it should appear.

Is there any way, I can do it with conditional formatting or any other means. I do not require macros or VBA code. For security purpose all macros are disabled.

Please suggest a method.

 

[SkipVought]

hi,

As I stated in your previous post in forum707, this has NOTHING to do with formats!

So where does this picture need to be with respect to cells A1:A10?

you could have a cell in a row other than 1:10 tnat counts occurrences in A1:A10 and if the count is not 10 then that row could be HIDDEN via AutoFilter.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JVFriederick]

Skip is correct (as usual).

Two possibilities THAT DO REQUIRE some vb code:

1) Check out John McGimpsey's site on this subject.

http://www.mcgimpsey.com/excel/lookuppics.html

2) Another possibility that you may find suitable.
-Insert the photos and rename each from 'Picture 1' etc. to a specific name using the xl menu Selection & Visibility.
-Stack the photos on top of each other where you wish them to be displayed.
-Add validation cell, for example B1, that contains list of new picture names.
-Add vb code to worksheet with a trigger pointing to cell B1.

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Pictures.Visible = False ' TURN OFF ALL PICTURES
If Not Application.Intersect(Target, Range("B1")) Is Nothing Then

ActiveSheet.Shapes.Range(Array(Target(1).Value)).Visible = msoTrue ' TURN ON PICTURE BASED ON VALUE OF CELL IN B1
​

End If
​

End Sub

I could not think of a solution without VB code, sorry. Will send the example XL file if you wish, just post email address.

Thanks for your question, you gave me an idea that may come in handy someday!

 

[JVFriederick]

Oopsy. Went past my speed limit. Previous code turns off display after other changes made on sheet.

Corrected code as follows :


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B1")) Is Nothing Then

Me.Pictures.Visible = False ' TURN OFF ALL PICTURES
ActiveSheet.Shapes.Range(Array(Target(1).Value)).Visible = msoTrue
​

End If

End Sub

 

[combo]

Another option, still with some vba, is to write your own user defined function (UDF). Next you can apply it in the worksheet and pass arguments to format shapes.
An example in my reply in thread707-1530763.


combo