Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi,
I am not sure if this is possible but here it goes. I am wondering if it is possible to place a picture into a spreadsheet by using an auto lookup of some kind. For example all the pictures would be in the same folder and would be called 1234.jpg, 1235.jpg and so on. What I would like is for cell A16 to display picture No 1234 if cell E6 has that number in it. Also could it resize the picture? If not its not a problem as resizing can be done before it is placed into the folder. Hope that make sense to someone and hope someone can help. Thanks in advance Carl |
#2
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
In the module of the worksheet, paste in the following code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim stFile As String If Not Intersect(Target, Me.Range("E6")) Is Nothing Then stFile = "C:\Temp\" & Me.Range("E6") & ".JPG" If Dir(stFile) = "" Then MsgBox "File not found" Else Me.Shapes("NewPic").Delete With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _ Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4) .Name = "NewPic" End With End If End If End Sub --- You will need to adjust the directory in the line beginning stFile = And you will need to name the picture currently in A16 as NewPic (select it, type the name NewPic in the box at the left end of the formula bar and press Enter) Then, when you enter the name of a picture file in E6, the relevant picture should appear in A16. It will be sized in 4 * 3 format - you can change that too by modifying "* 3 / 4" Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
![]() |
|||
|
|||
![]() Quote:
I am using 2007 and still getting to grips with it. Thanks for the reply. Carl |
#4
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Bill,
When you paste this program in a cell, it gives an error message. Is there a particular place or way that it needs to be pasted? I placed an equal sign in the cell I chose to paste it in so it will not show up as just text. -JFGatorAlum "Bill Manville" wrote: In the module of the worksheet, paste in the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim stFile As String If Not Intersect(Target, Me.Range("E6")) Is Nothing Then stFile = "C:\Temp\" & Me.Range("E6") & ".JPG" If Dir(stFile) = "" Then MsgBox "File not found" Else Me.Shapes("NewPic").Delete With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _ Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4) .Name = "NewPic" End With End If End If End Sub --- You will need to adjust the directory in the line beginning stFile = And you will need to name the picture currently in A16 as NewPic (select it, type the name NewPic in the box at the left end of the formula bar and press Enter) Then, when you enter the name of a picture file in E6, the relevant picture should appear in A16. It will be sized in 4 * 3 format - you can change that too by modifying "* 3 / 4" Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Alt+F11 to the Visual Basic editor
View Project Explorer Expand the Microsoft Excel Objects Double-click the worksheet in which the relevant cells reside The code window for that worksheet will appear in the upper right window; paste the code in there. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
JFGatorAlum wrote:
When you paste this program in a cell, it gives an error message Yes, it would, I said paste it in the module of the worksheet - you need to be in the visual basic editor to do that. See other post for details. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#7
![]() |
|||
|
|||
![]() Quote:
Private Sub Worksheet_Change(ByVal Target As Range) Dim stFile As String If Not Intersect(Target, Me.Range("E6")) Is Nothing Then stFile = "C:\Temp\" & Me.Range("E6") & ".JPG" If Dir(stFile) = "" Then MsgBox "File not found" Else ******Me.Shapes("NewPic").Delete****** With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _ Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4) .Name = "NewPic" End With End If End If End Sub The text between the stars is highlighted yellow. Carl EDIT: Ok I deleted that line and it seems to be working fine now. Thanks again for your help. Last edited by the-jackal : September 25th 08 at 10:49 AM |
#8
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
Yes.
See my original post. As I said there, you need to name the picture you currently have on the sheet NewPic in order that it has a picture to delete. Alternatively, when it highlights that line, drag the yellow arrow down one line and press F5 to continue execution from the next line. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#9
![]() |
|||
|
|||
![]() Quote:
|
#10
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
The-jackal wrote:
EDIT: Ok I deleted that line and it seems to be working fine now. Thanks again for your help. Having deleted that line you will end up with every picture you have asked for sitting on top of the others. That's why the line was there, to delete the last one when it adds a new one. Put the line back. And delete all the pictures except the most recent one. Ok just 1 more thing. The picture is really small. Same width as A column. Can I set it to make the picture width 4 columns eg A16 to D16 You can make the picture whatever size you like. Just set its Width and Height properties - either in the line that adds it or subsequently. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#11
![]() |
|||
|
|||
![]() Quote:
Thanks again for all the help. Last edited by the-jackal : November 5th 08 at 12:19 PM |
#12
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
The-jackal wrote:
Ok been using this but was wondering if I can get it to display the picture at say 20% of its original size. With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _ Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4) .Name = "NewPic" ' the next 3 lines are new: .LockAspectRatio = False .ScaleHeight 0.2, True .ScaleWidth 0.2, True End With Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#13
![]() |
|||
|
|||
![]() Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Inserting a picture or chart into one cell in Excel | Excel Worksheet Functions | |||
how do I insert picture into cell so vlookup can return picture? | Excel Worksheet Functions | |||
Inserting picture into excel based on # in the another cell | Excel Worksheet Functions | |||
inserting a picture into a cell | Excel Discussion (Misc queries) | |||
Inserting a picture | Excel Discussion (Misc queries) |