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 teams 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

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

--
Regards,
Tom Ogilvy



"Max" wrote:

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 teams 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

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" wrote in message
...
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

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




"Bernie Deitrick" wrote:

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" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Max" wrote in message
...
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




"Bernie Deitrick" wrote:

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" wrote in message
...
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

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" wrote in message
...
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




"Bernie Deitrick" wrote:

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" wrote in message
...
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

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 , so you can forward to me
your e-mail address.

Thank you for your help

Max






"Bernie Deitrick" wrote:

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" wrote in message
...
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




"Bernie Deitrick" wrote:

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" wrote in message
...
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