Inserting pictures into cells (Excel 2007)

[Max]

Hello All,
I have a football league table that automatically sorts the 20 teams into
the relevant positions in the league table.
What I would like to do is have the relevant team’s emblem positioned next
to the cell that contains the team name, each time the league table is
updated?
I have found some code that is available on this site which is almost
correct, but my knowledge of VBA is similar to that of a cow at a new gate
and I am unable to adapt it for multiple teams.
Thank you for your help.

Max

 

[Tom Ogilvy]

for each pic in Activesheet.pictures
' code you have adjusted to use the pic
Next

 

[Bernie Deitrick]

Max,

Let's assume that you have named the pictures/shapes the same as the team name (Giants,Jets,Raiders,
etc.), and the team names are entered/sorted in cells C2:C21

Dim myC As Range

For Each myC In Range("C2:C21")
Worksheets("Sheet1").Shapes(myC.Value).Top = myC.Top
Next myC

This code will move the shapes to line up with the top of the cell that currently has the team name
in it.

HTH,
Bernie
MS Excel MVP

 

[Max]

Hello Bernie,
Here is the code I got of a site from Macgimpsey and Ass.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
This worked for one shape/picture, but the wrong one and it hides the rest.
Your code I could not get to work.
The teams are sorted in a range alphabetically and the pictures are to the
right of each team name.

Sorry I pushed the "Yes"button by mistake.

Thank you for your help

Max

 

[Jon Peltier]

You need two loops:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim rCell As Range
Me.Pictures.Visible = False
For Each rCell In Range("F1:F10").Cells
With rCell
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
Next
End Sub


- Jon

 

[Bernie Deitrick]

Max,

The code that I posted worked for my test set, where the names of the picutres were the same as the
value of the cell, the pictures were visible, were lined up in a column next to the names of the
teams, and the rows were set tall enough to contain the picture.

If you send me your workbook privately, I will take a look at what needs to be changed.

HTH,
Bernie
MS Excel MVP

 

[Max]

Hello Bernie,
I don't doubt thta it works.
That will be very much appreciated if I can send it to you.

My e-mail address is (e-mail address removed), so you can forward to me
your e-mail address.

Thank you for your help

Max