Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Please could anyone advise how this could be done

    Many thanks

    Robert

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Image selected by Random No (2003)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
    Me.Image1.Picture = LoadPicture("Cocuments and Settings<username>My DocumentsMy Pictures" & Range("A1") & ".jpg")
    End Sub

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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
    Attached Files Attached Files

Posting Permissions

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