[Solved] How to change image based on a cell value
[Solved] How to change image based on a cell value
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
Re: How to change image based on a cell value
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.
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.
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.
Re: How to change image based on a cell value
Will you please elobarate with example ? Sincerely wait for reply.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
Re: How to change image based on a cell value
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.
Images intentionally were not placed in same position in the modified sample document.
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.
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.
Re: How to change image based on a cell value
Thank you. Your solution is worked as per my expections. Big thank you once again.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit