[Solved] Change image based on a cell value

[agi]

Edit: Split from [Solved] How to change image based on a cell value because that topic is solved so you need your own. If you had the same situation you could use the same solution. Please do not post in another's topic unless you are helping to solve their problem for them.

I just deleted your pictrures from the file (after saving them onto my HDD); and then I reinsert them into the document. Then I renamed them to "Red", "Yellow", Green". The new macro references them by the name.

Images intentionally were not placed in same position in the modified sample document.

Lights_Zizi64.ods

Code: Select all

'NEW routines

'_____________________________________________________________________________________________


Sub onChangeValue(oEvent As Variant)

 Dim newText As String		' New value of cell B1
 Dim lenText As Integer		' Length of it
 Dim oSheets As Variant		' All sheets of this workbook
 Dim oSheet1 As Variant		' Target Sheet
 Dim oSheet2 As Variant		' Source Sheet
 Dim ResultPicture As Variant
 Dim oDrawPage As Variant	' This DrawPage contains all source images
 Dim srcImage As Variant		' One of them
REM Macro responds to changes in B1 only 
	If oEvent.AbsoluteName <> "$Example.$B$1" Then Exit Sub
REM What the name of light?	
	newText = oEvent.FormulaLocal

	Select Case newText
		Case "Red"
			ShowHide_CalcImage("Red", "Show")
			ShowHide_CalcImage("Yellow", "Hide")	
			ShowHide_CalcImage("Green", "Hide")	
		Case "Yellow"
			ShowHide_CalcImage("Red", "Hide")
			ShowHide_CalcImage("Yellow", "Show")	
			ShowHide_CalcImage("Green", "Hide")
		Case "Green"
			ShowHide_CalcImage("Red", "Hide")
			ShowHide_CalcImage("Yellow", "Hide")	
			ShowHide_CalcImage("Green", "Show")
		Case else
			exit sub
	end select
End Sub
'_____________________________________________________________________________________________


Sub View_all_of_three_pictures

	ShowHide_CalcImage("Red", "Show")
	ShowHide_CalcImage("Yellow", "Show")	
	ShowHide_CalcImage("Green", "Show")	
End Sub
'_____________________________________________________________________________________________


Sub ShowHide_CalcImage(Pic_Int_Name, Visibility as string)	
'This routine vill show/hide pictures with the passed name on all of sheets of the actual document.

 Dim oDocument, oSheets, oSheet, oDP, oBitmaps, oShape as object
 Dim i, j, ShCount as integer
 
	oBitmaps = ThisComponent.createInstance( "com.sun.star.drawing.BitmapTable" )

	oDocument = ThisComponent 
	oSheets = oDocument.Sheets
	'oSheet = oSheets.getByName(Sheet_name)
	ShCount = oSheets.Count
	
	For j = 0 to ShCount-1
		oSheet = oSheets.getByIndex(j)   'get the sheets	
		oDP = oSheet.GetDrawpage()
		for i = oDP.getCount() -1 to 0 step -1
			oShape = oDP.getByIndex(i)
			if oShape.getName() = Pic_Int_Name  then
				if Visibility = "Hide" then
					oShape.Transparency = 100
				else 
					oShape.Transparency = 0
				end if
			end if
		next i
	next j	
	Thiscomponent.setModified(True) 
end sub
'_____________________________________________________________________________________________

This might work for a few images, but I have over 50 that I'm trying to display depending on value of the cell with drop down menu. I'm not gonna make a Case statement with 50 different cases, each one having 50 lines to show one pic and hide the 49 others. Then when I have to add a new picture, go back and edit the Case statement again?? No way.

What is the problem with first attachment Lights.ods? It works ok for me but I don't understand where to define the result image should be put into cell D2

[Zizi64]

This might work for a few images, but I have over 50 that I'm trying to display depending on value of the cell with drop down menu.

Then you must rework the macro code.

You can get an image with the macro by its number, you can get the manually selected (highlighted) one, etc... As you need it.

[agi]

I figured it out! Just had to insert an arbitrary image into the Target sheet so the macro could find an image to manipulate. Then right-click on the sheet tab and pick Sheet Events, then assign the macro to when Content is Changed.