Macro to display an image by cell value

spreadit · March 30, 2022, 9:49pm

I have a need to display an image, according to a text value in a cell.
This solution was found on Stack Overflow, but it is for Excel.

Sub Sample()
    Select Case Range("G11").Value
        Case "Picture 1": ShowPicture ("Picture 1")
        Case "Picture 2": ShowPicture ("Picture 2")
        Case "Picture 3": ShowPicture ("Picture 3")
        Case "Picture 4": ShowPicture ("Picture 4")
    End Select
End Sub

Sub ShowPicture(picname As String)
    '~~> The reason why I am using OERN is because it is much simpler
    '~~> than looping all shapes and then deleting them. There could be
    '~~> charts, command buttons and other shapes. I will have to write
    '~~> extra validation code so that those shapes are not deleted.
    On Error Resume Next
    Sheets("Sheet1").Shapes("Picture 1").Delete
    Sheets("Sheet1").Shapes("Picture 2").Delete
    Sheets("Sheet1").Shapes("Picture 3").Delete
    Sheets("Sheet1").Shapes("Picture 4").Delete
    On Error GoTo 0

    Sheets("Temp").Shapes(picname).Copy

    '<~~ Alternative to the below line. You may re-position the image 
    '<~~ after you paste as per your requirement
    Sheets("Sheet1").Range("G15").Select 

    Sheets("Sheet1").Paste
End Sub

The code appears to be easily modified.
This is useful because I have over 50 named images to be selected.

Here is my test spreadsheet:
Display-Pic-By-Cell-Value.ods (335.0 KB)

This contains the macro:

Sub DisplayPic()

    Select Case Range("Sheet1.A2").Value
        Case "a": ShowPicture ("Image 1")
        Case "b": ShowPicture ("Image 2")
        Case "c": ShowPicture ("Image 3")
        Case "d": ShowPicture ("Image 4")
    End Select
End Sub

Sub ShowPicture(picname As String)
    '~~> The reason why I am using OERN is because it is much simpler
    '~~> than looping all shapes and then deleting them. There could be
    '~~> charts, command buttons and other shapes. I will have to write
    '~~> extra validation code so that those shapes are not deleted.
    On Error Resume Next
    Sheets("Sheet1").Shapes("Image 1").Delete
    Sheets("Sheet1").Shapes("Image 2").Delete
    Sheets("Sheet1").Shapes("Image 3").Delete
    Sheets("Sheet1").Shapes("Image 4").Delete
    On Error GoTo 0

    Sheets("Temp").Shapes(picname).Copy

    '<~~ Alternative to the below line. You may re-position the image 
    '<~~ after you paste as per your requirement
    Sheets("Sheet1").Range("B2").Select 

    Sheets("Sheet1").Paste
End Sub

I noted that Calc names images ‘Image 1, Image 2 etc’.
Consequently, I changed ShowPicture value ‘Picture 1’ to ‘Image 1’ etc.
and the Case values to a, b, c, d

When I run the macro
[Select Case Range(“Sheet1.A2”).Value] is not defined.
Neither is [Sub ShowPicture(picname As String)]

[Select Case Range(“Sheet1.A2”).Value] seems to be logical, but it fails.
[Sub ShowPicture(picname As String)] - I don’t know how in excel, the macro knows that ‘picname’ refers to the image name.

Does anyone know how to modify this code to work in Libre Office Basic?

joshua4 · March 30, 2022, 10:21pm

I’m not sure that the macro you show is a good starting point. If you have just one sheet that needs to regularly show 1 of 50 images based on the value in a cell, and those 50 images are pretty much fixed, then it might be better to just loop through them and set 49 of them to not visible and the selected one to visible. Copy/paste stuff gets a little sticky pretty fast.

The idea would be to create a loop around

ThisComponent.getSheets().getByName("Sheet1").getDrawPage().getByIndex(loopIndex).Visible = True

Of course, you might test visibility and switch it only if it was not the correct state, in case there were artifacts in resetting the visibility state.

If you have other shapes/pictures/etc. on the page, you may have to test somehow within the loop before messing with an image.

Option Explicit

Sub TestSBI()
	ShowOnlyByIndex(1)
End Sub

Sub ShowOnlyByIndex(imageToShowIndex As Integer)
	Dim drawPage As Object
	Dim loopingIndex As Integer
	
	drawPage = ThisComponent.getSheets().getByName("Sheet1").getDrawPage()
	For loopingIndex = 0 to drawPage.getCount() - 1
	   If drawPage.getByIndex(loopingIndex).Visible <> False Then
	      drawPage.getByIndex(loopingIndex).Visible = False
	   EndIf
	Next loopingIndex
	drawPage.getByIndex(imageToShowIndex).Visible = True
End Sub

spreadit · March 31, 2022, 3:56pm

Thanks for that suggestion
.
The single sheet is the user interface
It is made up of query modules; each passing the calculated result to the module beneath it.
The queries are driven by drop box selection.
Each module has it’s own worksheet, that will ultimately be hidden.
.
The first module must deal with all the data.
My PC is: Intel(R) Core™ i5-4590 CPU @ 3.30GHz (8GB ram)
I haven’t timed the calculation but it’s around 250 milliseconds, so it’s fine.
This first result isn’t passed to the next module, meaning that the process runs in isolation.
.
The result of this query will generate the name of the image that may be required to display.
The user may want to see the image, to jog their memory for the next query.
Therefore a Display & Hide button (or toggle) will be required, to run the macro.
.
The intention was to store the images in their own worksheet.
I looked in the Gallery directory, and found no images; only two config files.
I have failed to find where they are stored, even though the path states ‘gallery’?
I figured that if I knew where they are stored, perhaps I could rename them with their actual names, to simplify their manipulation (just a thought).
.
For the upload process, all other images were deleted, to ensure that each image number sequentially relates to the image name.
On the ‘image store worksheet’, all the images are sequentially loaded in column A.
The LO names (Image 1… etc.) are listed in a column, and in the next column is their actual names.
.
This allows the possibility to query the images against the value from the first drop box (say by a ‘Display Image’ button).
I can use SMALL to bring the name, say ‘Image 33’, to a single cell, and then call that image to the predetermined display cell in the UI.
.
That is how I was thinking.
It may not take advantage of all the programming tricks, but for my level of programming, it is easily understood.
.
Consequently, we would have a:

Fixed cell containing the drop box selection (image name)
Button to launch the display macro
Fixed cell containing the extracted LO ‘Image x’ name
Fixed cell, where the image will display (the top left corner of the image)
Button to remove the Image

I studied the macro that you suggested, and I couldn’t see how the image name (drop box cell) is referenced, nor the cell where it would display.
.
That is why I have outlined how the spreadsheet is set up, and how it will be used; as this is likely relevant to the choice of macro to deal with the images.
.
Thanks again for taking the time to look at this.
Apologies for the long post
.
Edit: I have completed the LO ‘Image x’ extraction.
The user can now make the drop box selection, and this produces the associated image name (say Image 42).

This means that I simply (?) need to display the image that is the named value:
$Temp.$V$1 (Image 42) at $Sheet1.$I$1
and offer a means to delete it.

JohnSUN · March 31, 2022, 5:01pm

I think you are talking about this.

spreadit · March 31, 2022, 5:26pm

Thanks JohnSUN
In fact I downloaded your sample spreadsheet, when initially researching this issue.
.
I also found the Stack Overflow solution.
I pasted the Stack solution into the first post, because I better understood it
.
Here is your Macro:

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
	lenText = Len(newText)

	oSheets = ThisComponent.getSheets()
	oSheet1 = oSheets.getByIndex(0)
	ResultPicture = oSheet1.getDrawPage().getByIndex(0)
REM No text? No action!
	If lenText = 0 Then
		ResultPicture.GraphicURL = ""
		Exit Sub
	EndIf
	oSheet2 = oSheets.getByIndex(1)
	oDrawPage = oSheet2.getDrawPage()
	For i = 0 to oDrawPage.getCount()-1
		srcImage = oDrawPage.getByIndex(i)
		If Left(srcImage.getName(), lenText) = newText Then
			ResultPicture.GraphicURL = srcImage.GraphicURL
			Exit Sub
		EndIf
	Next i
REM Oops! No images with name from B1?
	ResultPicture.GraphicURL = ""
End Sub

In the last paragraph of my previous post, I added an edit, confirming that the required ‘LO Image x’ is now calculated.
Therefore a button could launch the macro where [.AbsoluteName <> “$Temp.$V$1”]
Another button perhaps could make the [LenText = 0] to delete the image.
.
I will study your code and see if it will work for my spreadsheet.

spreadit · March 31, 2022, 9:39pm

I created a new spreadsheet with two worksheets - a copy of your original.
I pasted an image into Example sheet, and renamed it Result, and pasted 3 images in Data sheet, and renamed them.
Set the macro to run on “Content Changed”
.
The macro functioned and changed the Result Image
.
Some Issues

[ResultPicture.Graphic = “”] produces the error “Object variable not set”
This is for both instances of this line of code.
I replaced it with Print “”
Instance 1 : Print “Goodbye”
Instance 2 : Print “Hello”
.
Goodbye appears when the reference cell is empty
Hello appears when the reference cell has different text to the image names.
Perhaps someone has thoughts on this object variable not being set?
.
The reference cell can contain the first word of an image name, and the macro functions!!!
IE. An image named “Black 1” can be called by entering “Black”.
However, after naming two images ‘Black 1’ & ‘Black 2’
Entering Black would display Black 1
Entering Black 1 would display Black 1
Entering Black 2 would display Black 2
.
Whether this is sailing close to the wind, I don’t know?
.
The macro will not run from a button.
It is written to run from any change to the Example sheet.
Somehow the code needs to be rewritten, to function from a button, rather than [oEvent.FormulaLocal]
.
Perhaps the code could then be copied to another macro for ‘hiding the image’ and simply point it at an empty reference cell, assuming that [ResultPicture.Graphic = “”] can be set.
.
So far, the macro will not work with my spread sheet
For some bizarre reason; when using Navigator
I can double click on image ‘Result’ and Navigator switches to sheet1, selects the image AND it displays the Column Number & Row Number (as expected).
HOWEVER
If I double click on any name of one of my stored images, the Column & Row Number is always P418
Yet P418 is empty (and it seems to be a random cell)!
.
This is weird, and it is presumably the cause of the macro not working, because the macro cannot find any image.
.
The only difference with image ‘Result’ and all the other images, is that it was copy & paste.
All the other images were ‘Inserted’.
Truly bizarre - let’s hope that it is not a bug
I guess that I must try pasting in the images.
… more on this later
.
Edit: Just checked further, and Navigator is simply showing the last selcted cell.
By coincidence, on sheet1 this was the ‘Result’ image LOL.
.
Actually, that’s annoying because now I really don’t know why the macro doesn’t work on my working spreadsheet.
I guess it will all be much clearer tomorrow.

spreadit · April 1, 2022, 6:11pm

The Display/Hide Images by Cell Value (macros) are working

Re the previous issues:

[ResultPicture.Graphic = “”]
The solution is to replace this line of code with the penultimate block of code, and point it at a transparent image.
I didn’t do this, because all my images are fixed, and chosen via a Text Box.
Instead, I left in place a Print “Warning”.
.
The reference cell can contain the first word of an image name
This appears to work fine, presuming that the user scrolls through the options to select.
.
The macro will not run from a button
This issue (relevant to my needs), has been rectified.
Therefore, we have JohnSUN’s original macro, that runs on event change - one image or another, always displays.
… and we have my version, which displays the image upon button command
… and a companion button macro, to hide the displayed image
.
So far, the macro will not work with my spread sheet
This situation has yet to be addressed.
It’s the next task.
Presumably, this work will throw up some cautions, if anyone employs these macros.

Newly Discovered Issues that could be problematic:

The result image provides the Aspect Ratio (AR)
(I am not sure if there is a workaround for this - it is to be investigated)
All the images must have the same AR, to match the AR of the result image.
This means that a ‘square’ and ‘letterbox’ image will display differently - one will be distorted.
.
This fact is worth knowing in advance, if scaling a large quantity of images in Gimp.
Having done it once; repeating the task isn’t appealing.
If the images in calc cannot be fixed to an aspect ratio, the solution will be to create (in Gimp) the required canvas, and paste all images into that canvas.
.
The AR issue can be witnessed in the example ods file (distorted pyramid).
.
The named ‘Result’ image is referenced by ‘Index’
With my level of ‘basic’ being limited, I was unable to reference this image by it’s name.
This may prove to cause messing around, whenever new images are added.
The macro expects the ‘Result’ image to be the first in the Index (0).
If it isn’t, then it’s position must be counted in Navigator.
.
Not an issue, but a word of caution
After loading any images, the spreadsheet must be first saved.
Otherwise, the macro will throw up errors (as I discovered, to the cost of my time)

Having said all that
The macros appear to be working fine.
I’m not posting them as The Solution as there may yet be further modifications.
If anyone can resolve the image name reference, or Aspect Ratio, issues, that would be a great help.
In the meantime, the working example (with both macros) can be downloaded here:
Display-Hide_images_by_cell_value.ods (384.9 KB)

Lupp · April 1, 2022, 9:50pm

Since the OQ (@spreadit) wanted to get the image to be displayed from a list, he may also want to create that list with the help of an automatically generated “Index of Images”.
See example:
imageIndex.ods (495.8 KB)

spreadit · April 1, 2022, 10:27pm

Oooh my goodness, that is a very nice tool to have.
Thank you very much Lupp.
As you correctly perceived; I can make good use of this code.
I’ll have a look at it in greater detail tomorrow.

PYS · April 2, 2022, 2:54pm

Hi @spreadit

One solution is not to define the graphic property of an existing shape but to create one with the “source” size.

To hide, simply delete the inserted shape.

I inserted this quickly in your code.
Display-Hide_images_by_cell_valueInit.ods (387,6 Ko)

ATB

spreadit · April 2, 2022, 6:41pm

@JohnSUN
@Lupp
@PYS
Thank you guys (so much)
.
@JohnSUN for the original code
.
@PYS for the ideal solution to the aspect ratio problem.
This is a huge usability upgrade to the code, as it means that images can be inserted, without having to pre-process them.
The image displays correctly.
That’s a big problem solved
.
@Lupp For providing a tool, that solved an otherwise ‘unsolvable’ problem!!!
.
The Mystery of The Lost Image
As of this moment, everything is unclear…
When the image was lost?
How this event came to pass?
Or even, whether it was ever an image?
.
What is clear, is that Lupp’s Image Indexer determined that the ‘Result’ image (for holding the requested image) is indexed at position 1, Sheet1, and there are no other images listed for Sheet1.
Sheet1 image index starts at 1.
It should start at 0.
.
This is why the Display Image macro would not function.
It was looking for the Result image at position 0.
… and there would be no way of knowing that this was the issue.
Navigator simply lists the images.
.
This problem would have left me a quivering wreck
.
Because this would be the outcome, you came to my rescue; perhaps karma for some good deed that I have done
.
Raiders of The Lost Ark
After making a backup, I added another sheet.
Corner clicking to copy Sheet1 would be no good, as it would copy the entire sheet
… but I did it anyway, for rigour; and the error was repeated.
.
Deleted the sheet, and added another.
This time I column copied just the work area, but not the Result image.
The transparent image was inserted, and Image Indexer was run.
The fresh image was indexed at position 25 (?)
The image was then renamed, and indexed to position 1 (What!)
Closed, and reopened the spreadsheet … same.
.
Deleted the sheet, and added another.
This time, I simply inserted the image.
Bingo!
Indexed to position 0.
This means that the problem lies in the work area of Sheet1.
.
Conclusion
It was the ‘Value Highlighting BUG’ wot done it M’lud
That’s the bug that I discovered last week, which is now queued, awaiting assignment.
.
What To Do?
My guess is that I can rebuild the Sheet1 work area, onto a new sheet, copy & pasting just the text, values, and formulae.
Hah!
Reformatting, and resizing columns & rows, will be small pain, compared to the meltdown that would have awaited me, if I hadn’t received your blessed code.
.
Obviously it’s Brownie Points all round, for the people who have contributed to this topic, but circumstance would have it (a simple twist of fate), that your stockholding is now bursting at the seams
.
I think that I will also add a comment to the bug report, outlining the knock on effect of the bug
(it surely must be the bug that caused this)
.
All’s well, that ends well.

spreadit · April 3, 2022, 1:02pm

Lost Image Found
It wasn’t an image after all.
It was an OLE Object: It was a Chart!
.
Somehow, this seems cruelly unfair.
Navigator has a section called Drawing Objects with an icon showing shapes.
These elements are not listed in the image index.
However, OLE Objects ARE listed in the image index.
.
There may be a good reason for this, but clearly it can (does) create confusion.
… particularly when referencing images by index position.
Would it not be better for them to have their own index?
Is there a reason for their inclusion in the image index (if they don’t display in it)?
Does anyone have any thoughts on this?
.
The Journey Was Tough
Talk about spiked pits, and rolling boulders … I found this out the hard way
However, @Lupp it threw up a potential upgrade to the Image indexer.
The issue was, that when the Image Indexer is run, it effectively wipes out the spreadsheet undo list.
BOOM!
They are gone, and they’re not coming back
.
For troubleshooting, this is a pain because ‘action - index’ means that the action cannot be undone.
Cut - Index - RePaste is an option, but the sheet isn’t exactly as it was.
My workaround was to keep loading new sheets, and adding a test image.
It was fine … it did the job (it was a life saver, in fact).
… but in using it as an interrogation tool, perhaps it could be modded to create another version that runs in it’s own spreadsheet.
.
Testing Scenario
Have the ‘suspect spreadsheet’, and the ‘Image Indexer spreadsheet’, both open.
If the Image Indexer could read the ‘suspect’ sheets, and create the index list, it would not affect the suspect sheets.
In this way, it would outwit Schrödinger’s cat
It’s an idea to consider.
In the meantime, it has done it’s job for me (really grateful)
.
The Lesson Learned
… is to be aware of OLE Objects, when referencing images by index position!

spreadit · April 3, 2022, 5:38pm

@PYS Once the task of finding the cause of the hidden object was over, I had time to examine your code.
I discovered that, not only had you fixed the aspect ratio issue
… but you had also replaced ‘ref by index position’ with ‘ref by cell name’.
The issue of OLE objects no longer exists
Thank you very much for doing that
What a relief!
.
I thought … time to tidy up the hideIMG code.
A big smile came to me, when I saw that you had already done this, with a very elagent solution.
More thanks for that
.
There was a slight wobble, when I noticed that image ‘blank’ was accumulating in Navigator (multiple instances).
However, when clicking to another spreadsheet, and clicking back; those instances were gone, leaving a single instance of image ‘blank’.
.
I gave the two macros (display & hide) a good testing.
Leaving an image displayed, and loading another, was rectified by simply clicking the hide button.
… leaving the impression that the program is very resilient
.
As a result of your work, I have transferred all my remaining Brownie Points to your account
.
Everything works so well, I believe that my next post will be to upload the solutions (with credits) to my original question Macro to display an image by cell value.
.
Note:
I’m now glad that I didn’t add a question mark to the thread post; because that title is now a definitive statement, and hopefully will be found by those in need.

Edit:
It came to me that I could insert your HideIMG code, prior to the DisplayIMG code.
It worked!
This means that if a user doesn’t hide the previous image, it doesn’t matter, because the code cleans that up.
Nice!

PYS · April 4, 2022, 10:14am

@spreadit thanks for the feedback

Lupp · April 4, 2022, 12:08pm

I’m not aware of having stated something to that effect. My above supplied code simply makes an index of all the shapes contained in the spraedsheet document which support the service com.sun.star.drawing.GraphicObjectShape.
If you also want to include different shapes (For form controls or OLE objects e.g.) you need to change the related condition. The mentioned code also doesn’t worry in any way about which one of all the shapes will be used to display a chosen image. That’s stuff for the “show-it-sub”.
The new attachment gives an example concerning an extended index which may be used to create selections by any kind of filtering.
imageIndex2.ods (663.7 KB)

spreadit · April 4, 2022, 2:36pm

@Lupp Thank you very much for that
By showing what else is in the index, it enables selection by index position.
Hahah!
Too late for this instance, but at least I did find out that it was an OLE object that was screwing up my positioning.
It’s a great tool.
When I get a moment, I will look into setting it up to access a target spreadsheet.

spreadit · April 7, 2022, 3:02pm

Macros to display & hide an image by cell value

Source images can be of differing aspect ratio.
Any previous image being displayed, is first removed.
Images are called by name (avoiding potential errors with index position)
User variables are first declared & grouped
Source Image names can be calculated on a helper sheet (change oSheetx) number
… to avoid renaming say [Image 1…Image 150]
The macro is fully commented, to aid comprehension.

Development assistance by @Lupp

Macro 3 - Hide the image by @PYS
This is a companion macro to macro 2.
The image is hidden upon command.

Note: Both macro 2 & macro 3 are contained in the file:
Display-Hide_images_by_cell_valueI (FINAL).ods (385.6 KB)

Thanks go to the contributors

Edit
After all that work, I somehow managed to get a typo in the file title, and the renamed file can’t be uploaded LOL.
Hahah!
I guess that pretty much sums up coding

Macro to display an image by cell value

Macros to display & hide an image by cell value

Contents