hi,

Lets say I have a list of countries that gets sorts out depending on
certain things. I need Excel to insert the flag of the country in the
next column.

Is this possible?

Thanks for the help.

Turn on the macro recorder while you insert your picture manually
(insert=Picture=From File)

then turn of the macro recorder and modify the code to react to your
situation.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
hi,

Lets say I have a list of countries that gets sorts out depending on
certain things. I need Excel to insert the flag of the country in the
next column.

Is this possible?

Thanks for the help.

thx... but that's not what I meant.

Let me explain myself a little better.

I have a ranking of countries in column B (USA, England, Congo, etc).
The ranking may change when new data is entered. Lets say that at the
beginning the list was B1 England, B2 USA, B3 Congo. Then later when I
enter new data, the list might go B1 USA, B2 Congo, B3 England, etc. I
need column C to display the flag of the country and change everytime
the ranking changes.

I tried with this routine but column C (flags) doesn't refresh when the
ranking changes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim rngFlags1 As Range
Dim pic As Picture, shp As Shape

On Error Resume Next
'Only insert the picture if it's in the area where they type
the Product Names
'Change "Products" to a range of cells where they'll be typing
in Product numbers
Set rngFlags1 = Intersect(Me.Range("Flags1"), Target)
On Error GoTo 0

If Not rngFlags1 Is Nothing Then 'They entered a product number
'Loop through each cell they entered in
' in case they copied several product numbers into several
cells
For Each rng In rngFlags1
'Remove the exisitng picture (shape) from the cell to the
right
For Each shp In Me.Shapes
If shp.TopLeftCell.Address = rng.Offset(0, 1).Address
Then shp.Delete
Next shp
'Insert the picture
On Error Resume Next
Set pic = ActiveSheet.Pictures.Insert("C:\Documents and
Settings\Sergio\My Documents\Flags\" & rng.Text & ".gif")
On Error GoTo 0
If Not pic Is Nothing Then 'The picture exists
With pic
.Height = rng.Offset(0, 1).Height
.Width = rng.Offset(0, 1).Width
.Left = rng.Offset(0, 1).Left
.Top = rng.Offset(0, 1).Top
End With

End If

Next rng

End If
End Sub

If the user is manually making changes in column B, then the event should
fire.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
thx... but that's not what I meant.

Let me explain myself a little better.

I have a ranking of countries in column B (USA, England, Congo, etc).
The ranking may change when new data is entered. Lets say that at the
beginning the list was B1 England, B2 USA, B3 Congo. Then later when I
enter new data, the list might go B1 USA, B2 Congo, B3 England, etc. I
need column C to display the flag of the country and change everytime
the ranking changes.

I tried with this routine but column C (flags) doesn't refresh when the
ranking changes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim rngFlags1 As Range
Dim pic As Picture, shp As Shape

On Error Resume Next
'Only insert the picture if it's in the area where they type
the Product Names
'Change "Products" to a range of cells where they'll be typing
in Product numbers
Set rngFlags1 = Intersect(Me.Range("Flags1"), Target)
On Error GoTo 0

If Not rngFlags1 Is Nothing Then 'They entered a product number
'Loop through each cell they entered in
' in case they copied several product numbers into several
cells
For Each rng In rngFlags1
'Remove the exisitng picture (shape) from the cell to the
right
For Each shp In Me.Shapes
If shp.TopLeftCell.Address = rng.Offset(0, 1).Address
Then shp.Delete
Next shp
'Insert the picture
On Error Resume Next
Set pic = ActiveSheet.Pictures.Insert("C:\Documents and
Settings\Sergio\My Documents\Flags\" & rng.Text & ".gif")
On Error GoTo 0
If Not pic Is Nothing Then 'The picture exists
With pic
.Height = rng.Offset(0, 1).Height
.Width = rng.Offset(0, 1).Width
.Left = rng.Offset(0, 1).Left
.Top = rng.Offset(0, 1).Top
End With

End If

Next rng

End If
End Sub

The user doesn't make changes to column B. Column B updates itself
after data is entered in another table.

Then you would need to use the calculate event.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
The user doesn't make changes to column B. Column B updates itself
after data is entered in another table.

and how do i do that?

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
and how do i do that?