Displaying Images based on a Result (Microsoft Excel 2002 build 10.2614.2625)

[Yancx]

Displaying Images based on a Result (Microsoft Excel 2002 build 10.2614.2625)

I have this Excel workbook that simulates a horse race. When I manually roll 3 dice and enter those rolls, the number on the dice which corresponds to a horse moves one place and an X is placed in that cell. What I would like to do is have a picture of a horse (a different picture for each horse in the race) to be placed in the cell instead of the X. I have been trying to use "Displaying Images based on a Result" without any success. I have watched videos using this technique but have not been able to duplicate it, perhaps because I am using Excel 2002. Any help would be appreciated.

Yancx

[kev_]

I could not afford to get you a horse, but I did manage a bicycle
- hopefully it looks like a bicycle in your version of Excel too!

see attached workbook


HorseRace.jpg

[Yancx]

Hi Kev,
That is almost what I want.
However I need a (JPG, BMP, GIF, etc.) picture file to appear where the bicycles now appear.
And yes, it does show up as a bicycle.
Thank you for your help.

Yancx

[kev_]

I have actually worked out a solution for you using jpegs and will post it for you tomorrow.
I think () it should work with your rather elderly version of Excel
It's very simple VBA
But I am not sure that I can create a macro enabled file that your Excel will open
- I need to do some homework!!
- or perhaps just post the VBA


gallop.jpg

[Yancx]

Kev, Thank you so much!

[kev_]

See how you get on with the attached file - the VBA is included - I hope it runs in Excel2002
- a lot of things have changed since then
- I have opted for simplicity to minimize likely issues
- nothing has been tested except with Excel2016

- Images have been named Nag_1 to Nag6
- the old nags are moved along by increment (which you can alter to any number you want)
- CommandButton1 is the dice
- CommandButton2 brings them back to the start
- CommandButton3 allows user to run a race without keep hitting the dice

If this runs for you, we can then develop it a little further to display Winner, 2nd, 3rd etc

HorsesToGo.jpg



All code is in the SHEET module

Please Login or Register  to view this content.

[Yancx]

Hi Kev,
WOW! It worked! Thank you so much. A couple of changes I need. I will actually roll 3 dice, then enter those values. (The dice rolls needs to stay visible on the work sheet) When I roll the 3 dice 3 horses will move one cell, (need to see the 11 cells like in your bicycle version.) The horse that reaches the 11th cell is the winner. Again, I appreciate your help. I've been trying to figure this out for over a month.

[kev_]

What a truly lucky break that it worked first time!
- will post update (based on post#7 comments) later today

[kev_]

Apologies - still tied up elsewhere.
Looks like it will be tomorrow now..

[Yancx]

Not a problem. I appreciate anytime you have and will be giving me.

Yancx

[kev_]

As requested:
- separate cells visible (so: horse's "location" is now driven by cell's "left" attribute)
- three dice thrown at same time
- record of previous throws retained
- winner is horse arriving 1st at the eleventh cell
- 3 dice "land" at same time
- if 2 cross line during same throw, horse with most points deemed winner, otherwise a tie

3 ways to play:
1. Throw "physical" dice and EITHER manually enter values in A1:C1 OR click on numbers 1:6 in column E
2. Click on "Throw Dice" for a "digital" throw
3. Click on "Computer Race" for a simulated race
- there is no need to reset before a simulated race
- all horses are dragged back by their tails to the start line automatically

Formulas:
Cells F2:H7 contain formulas etc as do Q2:S7

G2:G7 = cumulative points per horse (= named range "Points")
E2:H7 = lookup table to covert 1,2,3 to 1st,2nd,3rd etc (= named range "Placed")
F2:F7 = current "position" in race - based on this formula =VLOOKUP(RANK(G2,Points),Placed,4,0)

To highlight the winner:
Base formula driving values in Q2:Q7 (columns R:S take values from Q)
=IF(AND(MOD(COUNT(A:C),3)=0,G2>=11,F2="1st"),"Win!","")

Health & Safety:
- user is prevented from entering the racetrack for his own safety
- prevents him being endangered by rampaging horses
- coincidentally protects formulas
- protection enabled via this line of VBA in the Change Selection Event macro

Please Login or Register  to view this content.

HorsePlay.jpg

[Yancx]

Hi Kev, You nailed it! This is just what I want! Just one last thing though. Is it possible to change the horses colors to (in no particular order) to a vibrant red, orange, yellow, green, blue and purple? If not, it is perfect the way it is! I appreciate and thank you for the work and time you have given to me. Again, thank you so much!
Yancx

[kev_]

You are welcome
Your requested coloured horses are ready to race - see attached workbook

If that is everything, please mark the thread as SOLVED (thread tools, top of thread)

[Yancx]

Hi Kev, Thank you!