Microsoft Excel.
How do I display an image in an Excel cell based on the user’s selection in the dropdown of another cell?
The code below stops at the second last line:
imageData = imagePath.Picture
with the message ‘invalid qualifier’.
I am sure I have specified the correct worksheet and cell reference which contain the image. I don’t know if the surrounding syntax is correct.
Sub ShowImageBasedOnDropdown()
Dim ws As Worksheet
Dim dropdownValue As String
Dim imagePath As String
Dim pic As Picture
' Set the worksheet where the dropdown and target cell are located
Set ws = ThisWorkbook.Sheets("purchasessheet")
' Get the value from the dropdown (for example, E2)
dropdownValue = ws.Range("E2").Value
' Clear any existing pictures in the target cell (for example, D2)
For Each pic In ws.Pictures
If Not Intersect(pic.TopLeftCell, ws.Range("D2")) Is Nothing Then
pic.Delete
End If
Next pic
' Select the image path based on the dropdown value
Select Case dropdownValue
Case "Corn"
imagePath = ThisWorkbook.Sheets("lookupsheet").Range("J2")
Case "Rice bran"
imagePath = ThisWorkbook.Sheets("lookupsheet").Range("J3")
Case "Eggstock"
imagePath = ThisWorkbook.Sheets("lookupsheet").Range("J4")
' Add more cases for other dropdown values as needed
End Select
imageData = imagePath.Picture
Range("purchasessheet!D2").Picture = imageData
End Sub
Range("purchasessheet!D2").Picture = imageData
Range.Picture
is not a valid property or method of the Excel Range object.
Dynamic pictures is generally done without the use of VBA. The trick is to have a Shape’s formula = to a defined name that returns a range based on a formula that returns a range.
AwayBanner =OFFSET(INDEX(NFLTeams,MATCH(
AwayTeamChoose,NFLTeams,0)),0,-1,1,2) AwayTeamChoose =Settings!$B$12
GameTime =Settings!$B$15 HomeBanner =OFFSET(INDEX(NFLTeams,MATCH(
HomeTeamChoose,NFLTeams,0)),0,-1,1,2) HomeTeamChoose =Settings!$B$11
NFLTeams =’NFL Teams’!$B$2:$B$33
Watch: How to display images dynamically in Excel
2
The solution I have used could not be simpler. No VBA.
=XLOOKUP(E3, lookupsheet!$R$2:$R$4, lookupsheet!$S$2:$S$4)
1
If you have access to the new IMAGE function – Microsoft Support, you could try this:
- Define a table with names to show in drop-down and along with paths,
tbImage
in this example - Create a name for lookup based on table (cannot use tables directly yet),
imageNames
==tbImage[Name]
- Select cells for drop-downs and set the list as
imageNames
- In the first cell, enter this dynamic array formula formula
=LET(
selection, A7:A8,
IF(
ISBLANK(selection),
"",
IMAGE(XLOOKUP(selection, tbImage[Name], tbImage[Path]), selection)
)
)