Image per condition


I would like to know how to use an image instead of the standard conditional formatting icons. The data has a drop-down list of Blocker, Critical, Major, Minor, Low. Each has a related image for each option. Ideally, when the option is used the next cell would populate the image.  If Blocker, then the next cell shows the blocker image. 

Thank you! 



Selected Answer

 Hi and welcome to the Forum

You need to use VBA to do this. In the attached macro-enabled workbook I've devised one method.

Sheet 1 imagines your have several columns of project data, with column J as a status column (and K for the icon). I've used Data Validation (only A2:A16 currently but you can expand it) using the List method so the condition can be picked from a drop-down (or deleted). The validation Source setting is:

where Condition is a named column in the table on the sheet "Ref data". Next to that is a column "Image" which has the names of the icons/pictures I've used (or you want to- add them, deleting mine if you want to).

Provided you leave space above each image, there's a macro you can run to get the names that VBA knows the images as- the code is:

Sub GetShapeName()
' Go through the sheet and add the Shape name ABOVE it
Dim Shp As Shape

For Each Shp In Sheet2.Shapes 'loop through any shapes and...
    Shp.TopLeftCell.Offset(-1).Value = Shp.Name 'put the VBA name for each shape ABOVE it
Next Shp

End Sub
Once that's run, you need to put the names manually into the Image column. You could change one of the condition images to Picture 12 say (the bird image, just there for illustration).

The main work is done by a Worksheet_Change event macro behind Sheet1 (code below with explanatory comments). If you use a column other than J, change the letter in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("J")) Is Nothing Then Exit Sub

Dim Rng As Range, Cond As Range
Dim Imag As String, Shp As Object

'First remove any shpe next to the changed cell
For Each Shp In Sheet1.Shapes
    If Shp.Type = msoPicture Then
        If Shp.TopLeftCell.Address = Target.Offset(0, 1).Address Then Shp.Delete
    End If
Next Shp

'If no condition set, leave macro
If Target.Value = "" Then Exit Sub

'Otherwise, look for a matching condition in Sheet2
Set Rng = Sheet2.Range("Condition") 'look in table column
Set Cond = Rng.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If Not Cond Is Nothing Then 'if found
    Imag = Cond.Offset(0, 1).Value 'take image name
    Sheet2.Shapes(Imag).CopyPicture 'copy and paste it next to condition cell...
    Target.Offset(0, 1).PasteSpecial
    Target.Select 'reselect changed cell (rather than pasted image
    Target.Offset(0, 1) = ""
    If Target.Value <> "" Then MsgBox "Oops! Value not found in sheet " & Sheet2.Name
End If

End Sub
If you change a condition in J, the macro will remove the current icon and replace it in column K with the a picture of the one it finds listed in the table.

Once you're happy that's working, you can hide the Ref data sheet.

Hope this helps.



Awesome! This is exactly what I was looking for! THANK YOU! I am always looking to expand my knowledge with VBA! 
Qtwluv (rep: 2) Sep 12, '21 at 10:24 pm
Glad it works for you and thanks for selecting my Answer. It's not perfect in that the staus  images need to be scaled to fit your worksheet (I didn't do that in VBA) and if you filter the Status column, the icons can get stacked but you may find workarounds for those if needed.
John_Ru (rep: 1917) Sep 13, '21 at 4:05 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login