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]

Try this

Sub ChangeColour()
    ActiveSheet.Pictures.Delete
    Dim lastRow As Long, Pic As Variant, N As Variant
    Dim URLs As Range, URL As Range
 
    With ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set URLs = .Range("A2")     ' this range needs amending ???
    End With

    For Each URL In URLs
        URL.Offset(0, 4).Select
        URL.Parent.Pictures.Insert URL.Value
        DoEvents

        Set Pic = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
        N = Pic.Name
        MsgBox N
        If Not URL.Offset(, 1) Then ActiveSheet.Shapes(N).PictureFormat.ColorType = 2      
    Next
   
End Sub

What does message box return

 

[mickyflash]

Drop Down 1

 

[mickyflash]

and now drop down 2 (but I havent changed anything!)

 

[Yongle]

Weird............ but it explains why we are getting nowhere!!

Now run this ...

Sub ChangeColour()
    ActiveSheet.Pictures.Delete

    Dim lastRow As Long, Pic As Variant, N As Variant
    Dim URLs As Range, URL As Range
    
    With ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set URLs = .Range("A2")     ' this needs amending ???
    End With

    For Each URL In URLs
        URL.Offset(0, 4).Select
        URL.Parent.Pictures.Insert URL.Value
        DoEvents
    Next
    Dim shp As Shape, msg As String
    For Each shp In ActiveSheet.Shapes
        msg = msg & vbCr & shp.Name
    Next
    Debug.Print  Now & vbCr & msg
End Sub

What was printed to the immediate window ?
- paste whatever is in immediate window into your reply
- in VBA editor you can see immediate window with CTRL G

 

[mickyflash]

03-Dec-19 3:58:22 PM

Chart 7
Chart 8
Picture 11
Drop Down 3
03-Dec-19 3:58:28 PM

Chart 7
Chart 8
Picture 12
Drop Down 3
03-Dec-19 3:58:59 PM

Chart 7
Chart 8
Picture 13
Drop Down 3

 

[mickyflash]

chart 7 and chart 8 are graphs on the worksheet but are irrelevent to this and not important if they are causing the issue

 

[Yongle]

What is Drop Down 3 ?

 

[mickyflash]

its saying drop down 4 now!

The player name is selected by a drop down data validation.
The result is then VLOOKUP'd to a table which pulls the image link address into A2

 

[mickyflash]

removing the drop down list does not change and now we are on Drop Down 4

 

[Yongle]

OK
- at least we understand why the code was not returning the picture
- it (corrrectly) returned the last shape added BUT there is nothing in your code inserting a dropdown

What is causing a new dropdown to be inserted ?
- is any event code triggered when cell values change in the sheet ?
- look in the sheet code module or in ThisWorkbook module
- I can amend the code without the anser but I would like to understand what is going on behind the scenes

I am out of time for today but I will post amended code tomorrow