Insert picture based on cell value (unlimited)
 

I am looking to insert a picture based on a cell value. The exact application
is to insert the picture of a person based on choosing their name from a
drop-down list. Anybody have any suggestions; VBA or other? Thank you in
advance for the help.
--
djk44

Insert picture based on cell value (unlimited)
 

Record a macro whilst you insert the picture manually.

Then edit/include that in the required event, probably:
Private Sub ComboBox1_Click()
End Sub
where ComboBox1.Text gives you selected text.

Depends how you are matching the selected name to the required graphic.

NickHK

"djk44" wrote in message
...
I am looking to insert a picture based on a cell value. The exact
application
is to insert the picture of a person based on choosing their name from a
drop-down list. Anybody have any suggestions; VBA or other? Thank you in
advance for the help.
--
djk44

Insert picture based on cell value (unlimited)
 

Thanks for the input. I will be using a listbox control that returns a name
(using a vlookup function). When I select the name, the assigned name of the
image will flow into the worksheet. The macro can then lookup in the same
path, the exact image name and enter the image into a given cell range
location.

When I record the macro, this is the code.
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\Dave Knight\My Documents\My
Pictures\football.jpg"). _ '"football.jpg" is the image I would like to have
chosen by the value within a cell in the worksheet (ie. soccer.jpg,
baseball.jpg, etc.)
Select
Selection.ShapeRange.ScaleWidth 0.45, msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.45, msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.IncrementLeft -59.25
Selection.ShapeRange.IncrementTop -273#
Selection.ShapeRange.ScaleWidth 0.75, msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.75, msoFalse, msoScaleFromBottomRight

Anymore detailed help is greatly appreciated.

--
djk44


"NickHK" wrote:

Record a macro whilst you insert the picture manually.

Then edit/include that in the required event, probably:
Private Sub ComboBox1_Click()
End Sub
where ComboBox1.Text gives you selected text.

Depends how you are matching the selected name to the required graphic.

NickHK

"djk44" wrote in message
...
I am looking to insert a picture based on a cell value. The exact
application
is to insert the picture of a person based on choosing their name from a
drop-down list. Anybody have any suggestions; VBA or other? Thank you in
advance for the help.
--
djk44

Insert picture based on cell value (unlimited)
 

Based on that macro code and however you are getting the file name from the
combobox selection (called the function "GetFileNameFromValue" here), you
can

Private Sub ComboBox1_Click()
Dim FileName as string
Dim Pic As Picture

FileName = GetFileNameFromValue(ComboBox1.Text)

Set Pic = ActiveSheet.Pictures.Insert(FileName)

With Pic
.Left = 10
.Top = 10
.Width = 100
.Height = 100
End With

End Sub

<Side note
Pictures and the data type Picture are not visible in VBA help or the object
browser, but clear work. From the immediate window:
?typename(ActiveSheet.Pictures.Insert(Filename)
Picture

Maybe someone more knowledgable can explain why
</Side note

An alternative, more modern way is to use the .AddPicture

Dim ss As Shape
Set ss = ActiveSheet.Shapes.AddPicture(FileName, _
msoFalse, _
msoTrue, _
10, _
10, _
100, _
100)

This creates less flicker.

NickHK

"djk44" wrote in message
...
Thanks for the input. I will be using a listbox control that returns a
name
(using a vlookup function). When I select the name, the assigned name of
the
image will flow into the worksheet. The macro can then lookup in the same
path, the exact image name and enter the image into a given cell range
location.

When I record the macro, this is the code.
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\Dave Knight\My Documents\My
Pictures\football.jpg"). _ '"football.jpg" is the image I would like to
have
chosen by the value within a cell in the worksheet (ie. soccer.jpg,
baseball.jpg, etc.)
Select
Selection.ShapeRange.ScaleWidth 0.45, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.45, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.IncrementLeft -59.25
Selection.ShapeRange.IncrementTop -273#
Selection.ShapeRange.ScaleWidth 0.75, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.75, msoFalse,
msoScaleFromBottomRight

Anymore detailed help is greatly appreciated.

--
djk44


"NickHK" wrote:

Record a macro whilst you insert the picture manually.

Then edit/include that in the required event, probably:
Private Sub ComboBox1_Click()
End Sub
where ComboBox1.Text gives you selected text.

Depends how you are matching the selected name to the required graphic.

NickHK

"djk44" wrote in message
...
I am looking to insert a picture based on a cell value. The exact
application
is to insert the picture of a person based on choosing their name from
a
drop-down list. Anybody have any suggestions; VBA or other? Thank you
in
advance for the help.
--
djk44

Insert picture based on cell value (unlimited)
 

I'll try it and let you know how it goes.


--
djk44


"NickHK" wrote:


Based on that macro code and however you are getting the file name from the
combobox selection (called the function "GetFileNameFromValue" here), you
can

Private Sub ComboBox1_Click()
Dim FileName as string
Dim Pic As Picture

FileName = GetFileNameFromValue(ComboBox1.Text)

Set Pic = ActiveSheet.Pictures.Insert(FileName)

With Pic
.Left = 10
.Top = 10
.Width = 100
.Height = 100
End With

End Sub

<Side note
Pictures and the data type Picture are not visible in VBA help or the object
browser, but clear work. From the immediate window:
?typename(ActiveSheet.Pictures.Insert(Filename)
Picture

Maybe someone more knowledgable can explain why
</Side note

An alternative, more modern way is to use the .AddPicture

Dim ss As Shape
Set ss = ActiveSheet.Shapes.AddPicture(FileName, _
msoFalse, _
msoTrue, _
10, _
10, _
100, _
100)

This creates less flicker.

NickHK

"djk44" wrote in message
...
Thanks for the input. I will be using a listbox control that returns a
name
(using a vlookup function). When I select the name, the assigned name of
the
image will flow into the worksheet. The macro can then lookup in the same
path, the exact image name and enter the image into a given cell range
location.

When I record the macro, this is the code.
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\Dave Knight\My Documents\My
Pictures\football.jpg"). _ '"football.jpg" is the image I would like to
have
chosen by the value within a cell in the worksheet (ie. soccer.jpg,
baseball.jpg, etc.)
Select
Selection.ShapeRange.ScaleWidth 0.45, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.45, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.IncrementLeft -59.25
Selection.ShapeRange.IncrementTop -273#
Selection.ShapeRange.ScaleWidth 0.75, msoFalse,
msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 0.75, msoFalse,
msoScaleFromBottomRight

Anymore detailed help is greatly appreciated.

--
djk44


"NickHK" wrote:

Record a macro whilst you insert the picture manually.

Then edit/include that in the required event, probably:
Private Sub ComboBox1_Click()
End Sub
where ComboBox1.Text gives you selected text.

Depends how you are matching the selected name to the required graphic.

NickHK

"djk44" wrote in message
...
I am looking to insert a picture based on a cell value. The exact
application
is to insert the picture of a person based on choosing their name from
a
drop-down list. Anybody have any suggestions; VBA or other? Thank you
in
advance for the help.
--
djk44