Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a picture into a cell? | Excel Worksheet Functions | |||
how do I insert picture into cell so vlookup can return picture? | Excel Worksheet Functions | |||
insert a picture based on a formula | Excel Worksheet Functions | |||
insert picture from file based on cel value/content | Excel Worksheet Functions | |||
how to insert picture to the cell? | Excel Discussion (Misc queries) |