[Solved] How to change image based on a cell value

Discuss the spreadsheet application
Post Reply
drp_61
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

[Solved] How to change image based on a cell value

Post by drp_61 »

I want to change image as per the selection list made in one cell. Is it possible in openoffice calc ? I saw libreoffice calc and found one file but it contains macro and produces error. Is it possible to rectify the error ? In microsoft excel, using match , index and in formulas menu using display names option it is possible. Sincerely wait to receive reply.
Attachments
Lights.ods
Files is made in libreoffice calc.
(40.62 KiB) Downloaded 171 times
Last edited by drp_61 on Tue Dec 25, 2018 2:10 pm, edited 1 time in total.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to change image based on a cell value

Post by Zizi64 »

The NAME of the visible picture is "Result" but not "Red" or "Yellow" or "Green" in your sample file.


Just a tip:
You can operate with the visibility (trasparency) of the embedded object. Put all of three pictures into the cell in same position, and change the transparency value of the pictures by your macro code.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
drp_61
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

Re: How to change image based on a cell value

Post by drp_61 »

Will you please elobarate with example ? Sincerely wait for reply.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to change image based on a cell value

Post by Zizi64 »

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
(40.22 KiB) Downloaded 250 times

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
'_____________________________________________________________________________________________
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
drp_61
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

Re: How to change image based on a cell value

Post by drp_61 »

Thank you. Your solution is worked as per my expections. Big thank you once again.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
Post Reply