Display image in cell based on VLookup value

n00dnutt · May 16, 2025, 5:45am

Hi Team

First-time poster and, Libre Calc user; after decades of VBA.

Proof-of-Concept project to better familiarise myself with “Basic”: I am building a Slot/Pokies Machines.

I have a VLookup Range: Coding!$B$2:$C$10. Currently $C2:$C$9 has “Special Characters” that display the picture (In the Reel Window), based on the matching value.

I use a VLookup (which works perfectly), but! I am looking to go the extra step, improve upon the “Special Characters” by using Shapes/Images.

Can Calc/Basic do this…?? I am thinking of a SELECT CASE:

(MY VBA Eqivalent pseudo-kinda example)

Blockquote

Dim rShapeVal AS Range: Set rShape = Sheets(“Coding”).Range(“B2:C10”)

Select Case rShapeVal
Case = 1
myShape01.Background.Image.Name=“Img01”
Case = 2
myShape02.Background.Image.Name=“Img02”

Etc… to myShape15

End Select

Blockquote

I have total of 15 “Cells” (5 Columns [Reels] x 9 Rows). Only 3 of the rows are visible as these are the [Pay-lines]).

TIA
Mark.

JohnSUN · May 16, 2025, 6:04am

I’m not sure if this is the trick you’re asking about.

Zizi64 · May 16, 2025, 6:22am

Here is an another sample:

ShowShape.ods (15.2 KB)

bantoniof · May 17, 2025, 10:18am

In The example of @Zizi64 you must comment the line number 12 (load xray)

Zizi64 · May 17, 2025, 10:44am

Fixed:

ShowShape2.ods (15.3 KB)

Villeroy · May 17, 2025, 11:48am

https://www.mediafire.com/file/b4p5155c1y8bhoq/Headshots.zip/file
is a database sample showing pictures based on filter criteria without a single line of macro code.

n00dnutt · May 18, 2025, 4:43am

Hi @Villeroy No! not filtered. It’s a stepped process.

The “Screenplay” sheet displays the VLookup result for other formulae on the “Coding” sheet.

Example: Special Character Images
Top left formula is: =$Coding.$G5
Below it formula is: =$Coding.$G6
Below it formula is: =$Coding.$G7

n00dnutt · May 18, 2025, 4:47am

The formulae that these “Screenplay” image refer to are:

The “Special Characters” are based on another lookup via the single digit numbers that are randomised: =RANDBETWEEN(1,9)

n00dnutt · May 18, 2025, 4:48am

Finally, the randomised numbers then lookup the corresponding “Special Character”.

Please disregard the double-digits as they were for references only and will be deleted soon.

karolus · May 18, 2025, 5:32am

I am pretty sure any of these IMAGES can be replaced by a real Unicode-character without any hazzle displaying IMAGES …

Villeroy · May 18, 2025, 3:58pm

Quite close: Ω‡ҖΨ♠♣♥♦

Villeroy · May 20, 2025, 3:16pm

Simple database solution with no animation nor macro code.
Slot_Machine.zip (37.3 KB)
Extract everything to one folder, open the database document and the form “Play” therein.

JohnSUN · May 19, 2025, 12:48pm

Well, Mark, please try this - Demo Animated Slot Machine.ods (46.0 KB)

I hope the comments in the code will be enough to understand how it was done. If not, feel free to ask questions, we are always happy to help.

n00dnutt · May 20, 2025, 12:35am

Hi @JohnSUN

The Demo is amazing, thank you, plus it has lots of code for me to pick apart (Reverse Engineer if you like).

I will be offline for a few weeks as I am selling my house and need time to fix it up. I will dive into the code after that and get back to you.

Take care, thank you so much for your guidance and time.

Warm regards
Mark.

n00dnutt · May 20, 2025, 11:31pm

Hi All

A big thank you to JohnSUN, bantoniof, karolus, Villeroy, Zizi64 .

All ideas and input have been amazing. From my perspective, this brings a conclusion to this thread.

A demo: “Animated Slot Machine” will be posted for your scutiny (thank you @JohnSUN for your guidance).

Given the collective talent here, I’m confident you can build it into something of a community project.

Warm regards
Mark.

schiavinatto · May 21, 2025, 1:43am

Hi @JohnSUN, thanks for sharing the “Demo Animated …” file, I tried to find out what the parameter is to finish faster, but I couldn’t find it. Can you indicate which parameter to change?

JohnSUN · May 21, 2025, 4:08am

Hi @schiavinatto !
If “finish faster” means “to stop the demo immediately”, then replace If bAlreadyRunning Then Exit Sub with If bAlreadyRunning Then End (line 28) - in this case, clicking the lever will start the animation, and clicking it again will stop it.

If by “finish faster” you mean “don’t spin the reels for so long”, then reduce the parameters in the RndBtw() function calls. For example, it was aCounts = Array(RndBtw(20, 40),.. - make aCounts = Array(RndBtw(2, 4),.., it was aCounts(j+1) = RndBtw(10, 20) - it will be aCounts(j+1) = RndBtw(1, 2)

If by “finish faster” you mean “spin the reels faster”, then try changing Wait 50 in line 132 - this is the pause between individual animation frames. (I vaguely remember that a value less than 50 does not work well, but there were reports about this a long time ago, back in the days of OpenOffice, before LibreOffice appeared - it’s worth trying)

If you want to cut the bar owner’s funny monologue at the end of the presentation, leaving only the reels spinning and stopping, then look at the code and comments in lines 115-126

I hope I was able to help

schiavinatto · May 21, 2025, 11:06pm

Thanks @JohnSUN, I’ll study it calmly.