A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Inserting a Picture using Cell as picture name.



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2008, 02:07 PM posted to microsoft.public.excel.links
the-jackal[_4_]
external usenet poster
 
Posts: 1
Default Inserting a Picture using Cell as picture name.


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




--
the-jackal
  #2  
Old September 24th, 2008, 09:52 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Inserting a Picture using Cell as picture name.

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  
Old September 24th, 2008, 10:52 AM posted to microsoft.public.excel.links
the-jackal[_5_]
external usenet poster
 
Posts: 1
Default Inserting a Picture using Cell as picture name.


Bill Manville;728252 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


Thats great and I hope it works, however there is only 1 problem. I
have never used Modules so dont know where to begin. I will look around
the forum for a guide and let you know how I get on.
I am using 2007 and still getting to grips with it.

Thanks for the reply.

Carl




--
the-jackal
  #4  
Old September 24th, 2008, 01:43 PM posted to microsoft.public.excel.links
JFGatorAlum
external usenet poster
 
Posts: 1
Default Inserting a Picture using Cell as picture name.

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  
Old September 24th, 2008, 11:53 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Inserting a Picture using Cell as picture name.

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  
Old September 24th, 2008, 11:53 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Inserting a Picture using Cell as picture name.

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  
Old September 25th, 2008, 09:29 AM posted to microsoft.public.excel.links
the-jackal[_6_]
external usenet poster
 
Posts: 1
Default Inserting a Picture using Cell as picture name.


Bill Manville;728860 Wrote:
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


Ok done that but i get an error and when I debug it shoes this.

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




--
the-jackal
  #8  
Old September 25th, 2008, 02:01 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Inserting a Picture using Cell as picture name.

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  
Old September 25th, 2008, 02:07 PM posted to microsoft.public.excel.links
the-jackal[_7_]
external usenet poster
 
Posts: 1
Default Inserting a Picture using Cell as picture name.


the-jackal;728863 Wrote:
Ok done that but i get an error and when I debug it shoes this.

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.


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




--
the-jackal
  #10  
Old September 25th, 2008, 05:49 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Inserting a Picture using Cell as picture name.

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.