VBA code for sizing/showing pictures

I'm using the following code to size and display a specific picture based on
my list box in cell A4. It does a "VLOOKUP" from cell A5 and then displays
the picture that is tied to the selection in the list box. This code is
specific to cell A5 only. I need to duplicate this function to cell C4 and
cell C5. But I want the picture in cell C5 to be driven by the listbox in
cell C4. How do I set this up since my code is cell specific? I want these to
be independent of each other to allow users to be able to choose a picture
for A5 and then another picture for C5. Here is the VBA code that drives the
A4 listbox and then puts the picture in A5:

Private Sub Worksheet_Calculate()

Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.ShapeRange.LockAspectRatio = msoFalse
oPic.Top = .Top
oPic.Left = .Left
oPic.Height = .Height
oPic.Width = .Width
oPic.Placement = xlMoveAndSize
Exit For
End If
Next oPic
End With
End Sub

Looks like John McGimpsey's lookuppics code.

Only allows for one image at a time to be displayed.

Got to Debra Dalgeish's site for a sample workbook from Bernie Dietrick for
adding pictures to selections.

Alows for more than one picture to be displayed based upon a value or item
chosen from DV lists

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection

Edit the code to suit.


Gord Dibben MS Excel MVP

On Wed, 27 Aug 2008 17:51:19 -0700, Secret Squirrel
<secretsquirrel.DeleteThis@discussions.microsoft.com> wrote:

>I'm using the following code to size and display a specific picture based on
>my list box in cell A4. It does a "VLOOKUP" from cell A5 and then displays
>the picture that is tied to the selection in the list box. This code is
>specific to cell A5 only. I need to duplicate this function to cell C4 and
>cell C5. But I want the picture in cell C5 to be driven by the listbox in
>cell C4. How do I set this up since my code is cell specific? I want these to
>be independent of each other to allow users to be able to choose a picture
>for A5 and then another picture for C5. Here is the VBA code that drives the
>A4 listbox and then puts the picture in A5:
>
> Private Sub Worksheet_Calculate()
>
> Dim oPic As Picture
> Me.Pictures.Visible = False
> With Range("A5")
> For Each oPic In Me.Pictures
> If oPic.Name = .Text Then
> oPic.Visible = True
> oPic.ShapeRange.LockAspectRatio = msoFalse
> oPic.Top = .Top
> oPic.Left = .Left
> oPic.Height = .Height
> oPic.Width = .Width
> oPic.Placement = xlMoveAndSize
> Exit For
> End If
> Next oPic
> End With
> End Sub