# Thread: Image selected by Random No (2003)

1. ## Image selected by Random No (2003)

I have a formula in cell A1 that returns a number between 1 and 50 (based on a random No formula). I need an image control to display an associated picture (from My DocumentsMy Pictures).

Eg. If A1 = 5, the Image control would display the picture My documentsMy Pictures5.jpg

Many thanks

Robert

2. ## Re: Image selected by Random No (2003)

Wouldn't the Worksheet Calculate event be a better place to do this? Does the recalculation of the random number generator function cause a worksheet change event?

3. ## Re: Image selected by Random No (2003)

I agree with Legare. A formula in A1 will not change even when the value does and thus the change event will not be triggered.

Steve

4. ## Re: Image selected by Random No (2003)

Yes, that was a mistake. Thanks, I'll add a comment to my reply.

5. ## Re: Image selected by Random No (2003)

Edited by HansV to correct mistake - I originally used the Worksheet_Change event instead of the Worksheet_Calculate event. Thanks to Legare for pointing out my error.

Right-click the sheet tab.
Select View Code from the popup menu.
Create the following event procedure:

Private Sub Worksheet_Calculate()
On Error Resume Next
End Sub

6. ## Re: Image selected by Random No (2003)

There is a non-code way, if you embed your pictures into the file. Obviously, this makes the file larger but also makes it easier to distribute. See attached.

7. ## Re: Image selected by Random No (2003)

Thank you, everyone. Hans' solution works fine.
However, I am very intrigued as to how Rory created his solution. This works as well, but I can't understand how ?

Robert

8. ## Re: Image selected by Random No (2003)

If you click once on the picture in Sheet1, you'll see that the formula bar says =GetPic.
You can look up the definition of GetPic in Insert | Name | Define..., it is

=OFFSET(Pictures!\$A\$1,0,Sheet1!\$A\$1)

If Sheet1!A1 = 2, this formula is equivalent to =Pictures!C1 (offset two columns to the right from A1), etc.

9. ## Re: Image selected by Random No (2003)

Rory's file can be modified to use pictures of different sizes though this requires naming each picture and using INDIRECT for the named formula.

It could be done with offset as well (and not naming the pictures), but then some kind of lookup table of start, rows, and columns for the picture would have to be included (or some way to calculate them)

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•