Image selected by Random No (2003)

Topic

New Reply

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

Please could anyone advise how this could be done

Many thanks

Robert

Reply | Quote

Replies

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
Me.Image1.Picture = LoadPicture(“C:Documents and SettingsMy DocumentsMy Pictures” & Range(“A1”) & “.jpg”)
End Sub

where is your username and Image1 is the name of the control.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote