change colour of picture based on cell value

[mickyflash]

hello again guys

I have found this video of Bills that explains how I could dynamically change the .width & .Height of a shape in excel, but I am struggling to convert this to the shapes colour?

There would be only 2 options, if the linked cell is = to TRUE to view the picture In colour, or greyscale if FALSE

Thanks in advance for your help

 

[Yongle]

Going back to your original code what is the name given by Excel to the image inserted in E4 ?

 

[mickyflash]

the first time the macro is run, its Picture1, after the DELETE the next its Picture2 and so on

 

[Yongle]

With "Picture 1" in the cell E2 what happens when you run this code ?

Sub ChangeColour()
    ActiveSheet.Shapes("Picture 1").PictureFormat.ColorType = 2
End Sub2

 

[mickyflash]

this works as an independent code

 

[Yongle]

Then this should work

Go back to your ORIGINAL code

1.
add this as declaration with others at top of module

Dim pic As Variant

2.
Below: URL.Parent.Pictures.Insert URL.Value
insert:

    Set pic = URL.Parent.Shapes(URL.Parent.Shapes.Count)
    If Not URL.Offset(, 1) Then pic.ColorType = 2

 

[mickyflash]

Thank you again for trying so much.. BUT

using this code:

I am still getting a 'object does not suport this property or method' debug error when the cell value is FALSE.
works OK when TRUE

 

[Yongle]

oops ..

✖BAD:

pic.ColorType = 2

✔GOOD:

pic.PictureFormat.ColorType = 2

 

[mickyflash]

different error code this time relating to the same area of code

 

[Yongle]

After this line

Set Pic = URL.Parent.Pictures.Insert(URL.Value)

Insert this line

MsgBox Pic.Name

what does the message box return

 

[mickyflash]