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. |
Zizi64 wrote: ↑Tue Dec 25, 2018 12:14 pm 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