External images

Gav · May 5, 2004

I am out of ideasand am after some lateral thinking. I have a folder containing images that i would like to make available within a workbook. I dont know if i should simply use a link to the folder or if there is a better way. There are approx 30 images within the folder and the workbook may only require 5-6 of them.

Any ideas??

Rob Bovey · May 5, 2004

Gav said:
I am out of ideasand am after some lateral thinking. I have a folder

containing images that i would like to make available within a workbook. I
dont know if i should simply use a link to the folder or if there is a
better way. There are approx 30 images within the folder and the workbook
may only require 5-6 of them.

Hi Gav,

To make an informed recommendation on this we would need more
information. Here are some examples of the additional information required
and why it matters in the decision making process:

1) What are the size of these images? - If they are relatively small, the
simplest route may be to simply place them all on a hidden sheet in the
workbook where they would be readily available.

2) How often do these images change? - If they change frequently, placing
them within the workbook is probably not a good idea because an updated set
of images would not propagate to existing workbooks. Sub-question - does it
matter if new images don't propagate to existing workbooks?

3) Will the image path be the same for every user of this workbook? - If
not, you will either need to write the code required for the user to located
the image folder on their system or put all the images in the workbook.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav · May 5, 2004

thanks ro

In response, the images are around 100-200KB each, depending. The location would be the same for each user on their local hard drive (my documents). The images would rarely change...it would serve only as a database of images for the user. Any new images would be updated directly into the target folder

This ofcourse is only an idea i had and would be open to alternatives

Cheers!!!!

Rob Bovey · May 5, 2004

Hi Gav,

If that's the case then you should probably leave the images in the
folder and write code to allow the user to select the image they want. I did
forget to ask what exactly you wanted to do with the image, but regardless,
here's some code to get you started. The most complicated part is getting
the path to the My Documents folder on the user's computer. Allowing them to
select an image and stick it on a worksheet is very easy.

Private Const S_OK As Long = 0
Private Const SHGFP_TYPE_CURRENT As Long = 0
Private Const CSIDL_PERSONAL As Long = 5
Private Const MAX_PATH As Long = 256

Private Declare Function SHGetFolderPathA Lib "Shell32.dll" _
(ByVal hWndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal szPath As String) As Long

Public Sub InsertImage()
Dim szPath As String
Dim vFullName As Variant
szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 0 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName <> False Then
Sheet1.Pictures.Insert CStr(vFullName)
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

Private Function szGetMyDocsPath() As String
Dim szPath As String
szPath = String$(MAX_PATH, vbNullChar)
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT, _
szPath) = S_OK Then
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar))
End If
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
thanks rob

In response, the images are around 100-200KB each, depending. The location

would be the same for each user on their local hard drive (my documents).
The images would rarely change...it would serve only as a database of images
for the user. Any new images would be updated directly into the target
folder.

Gav · May 5, 2004

thanks again bob....before i go any further, i have a query......to ditermine where the image pastes, can i use a heading??

IE: the sheet has certain sub headings like 'machine' and 'roller'. Can i use these headings as the targets in some way. I would like the image to paste into the empty cell beneath the heading. For each heading, the user would select an image

Thanks!!!!!

----- Rob Bovey wrote: ----

Hi Gav

If that's the case then you should probably leave the images in th
folder and write code to allow the user to select the image they want. I di
forget to ask what exactly you wanted to do with the image, but regardless
here's some code to get you started. The most complicated part is gettin
the path to the My Documents folder on the user's computer. Allowing them t
select an image and stick it on a worksheet is very easy

Private Const S_OK As Long =
Private Const SHGFP_TYPE_CURRENT As Long =
Private Const CSIDL_PERSONAL As Long =
Private Const MAX_PATH As Long = 25

Private Declare Function SHGetFolderPathA Lib "Shell32.dll"
(ByVal hWndOwner As Long,
ByVal nFolder As Long,
ByVal hToken As Long,
ByVal dwFlags As Long,
ByVal szPath As String) As Lon

Public Sub InsertImage(
Dim szPath As Strin
Dim vFullName As Varian
szPath = szGetMyDocsPath() & "\
If Len(szPath) > 0 The
ChDrive szPat
ChDir szPat
vFullName = Application.GetOpenFilename(
"Image Files (*.jpg),*.jpg", , "Select an Image"
If vFullName <> False The
Sheet1.Pictures.Insert CStr(vFullName
End I
Els
MsgBox "My Documents folder not found.
End I
End Su

Private Function szGetMyDocsPath() As Strin
Dim szPath As Strin
szPath = String$(MAX_PATH, vbNullChar
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT,
szPath) = S_OK The
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar)
End I
End Functio

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Gav said:
thanks ro

would be the same for each user on their local hard drive (my documents)
The images would rarely change...it would serve only as a database of image
for the user. Any new images would be updated directly into the targe
folder

Rob Bovey · May 6, 2004

Hi Gav,

I've made a modification to the code (shown below) that causes the top
left corner of the specified picture to be positioned in the top left corner
of the cell directly below the current selection. So if the user selects the
header cell prior to running the macro it should do what you want.

Private Const S_OK As Long = 0
Private Const SHGFP_TYPE_CURRENT As Long = 0
Private Const CSIDL_PERSONAL As Long = 5
Private Const MAX_PATH As Long = 256

Private Declare Function SHGetFolderPathA Lib "Shell32.dll" _
(ByVal hWndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal szPath As String) As Long

Public Sub InsertImageBelowRange()
Dim objImage As Picture
Dim rngCell As Range
Dim szPath As String
Dim vFullName As Variant
Set rngCell = Selection.Offset(1, 0)
szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 0 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName <> False Then
Set objImage = Sheet1.Pictures.Insert(CStr(vFullName))
objImage.Top = rngCell.Top
objImage.Left = rngCell.Left
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

Private Function szGetMyDocsPath() As String
Dim szPath As String
szPath = String$(MAX_PATH, vbNullChar)
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT, _
szPath) = S_OK Then
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar))
End If
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
thanks again bob....before i go any further, i have a query......to

ditermine where the image pastes, can i use a heading???

IE: the sheet has certain sub headings like 'machine' and 'roller'. Can i

use these headings as the targets in some way. I would like the image to
paste into the empty cell beneath the heading. For each heading, the user
would select an image.

Dave Peterson · May 6, 2004

I bet Rob meant:

szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 1 Then

instead of:

szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 0 Then

(since Rob appended "\" to szPath, the length would always be larger than 0.)

Gav · May 6, 2004

Gday rob

How do i run this code?? Does will it insert images in the cell beneath my headings?? There are 5 headings in total and each heading may have a correpsonding image

Cheers!!!!
----- Rob Bovey wrote: ----

Hi Gav

I've made a modification to the code (shown below) that causes the to
left corner of the specified picture to be positioned in the top left corne
of the cell directly below the current selection. So if the user selects th
header cell prior to running the macro it should do what you want

Private Const S_OK As Long =
Private Const SHGFP_TYPE_CURRENT As Long =
Private Const CSIDL_PERSONAL As Long =
Private Const MAX_PATH As Long = 25

Private Declare Function SHGetFolderPathA Lib "Shell32.dll"
(ByVal hWndOwner As Long,
ByVal nFolder As Long,
ByVal hToken As Long,
ByVal dwFlags As Long,
ByVal szPath As String) As Lon

Public Sub InsertImageBelowRange(
Dim objImage As Pictur
Dim rngCell As Rang
Dim szPath As Strin
Dim vFullName As Varian
Set rngCell = Selection.Offset(1, 0
szPath = szGetMyDocsPath() & "\
If Len(szPath) > 0 The
ChDrive szPat
ChDir szPat
vFullName = Application.GetOpenFilename(
"Image Files (*.jpg),*.jpg", , "Select an Image"
If vFullName <> False The
Set objImage = Sheet1.Pictures.Insert(CStr(vFullName)
objImage.Top = rngCell.To
objImage.Left = rngCell.Lef
End I
Els
MsgBox "My Documents folder not found.
End I
End Su

Private Function szGetMyDocsPath() As Strin
Dim szPath As Strin
szPath = String$(MAX_PATH, vbNullChar
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT,
szPath) = S_OK The
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar)
End I
End Functio

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Gav said:
thanks again bob....before i go any further, i have a query......t

ditermine where the image pastes, can i use a heading??use these headings as the targets in some way. I would like the image t
paste into the empty cell beneath the heading. For each heading, the use
would select an image

Rob Bovey · May 6, 2004

Hi Dave,

Yeah, that was exactly what I meant! How'd you know? <g>

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Dave Peterson said:
I bet Rob meant:

szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 1 Then

instead of:

szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 0 Then

(since Rob appended "\" to szPath, the length would always be larger than

0.)

Rob Bovey · May 6, 2004

Hi Gav,

Select one of the cells and run the code. This will insert the image for
that heading. Select the next setting and run the code again. Continue for
all additional headings. If your headings will always be located in the same
cells you could run the code inside a loop that iterates through all of
those cells

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
Gday rob,

How do i run this code?? Does will it insert images in the cell beneath my

headings?? There are 5 headings in total and each heading may have a
correpsonding image.

Gav · May 6, 2004

Gday again.........ok, let me see......i select the cell under the heading, click tools, macro, select 'run' for the code. It brings the my documents window up but when i select an image, nothing occurs.

The images will always remain 1 row under the heading of the section. SO it may be a good idea to loop. How is this done?

PS: if it is easier to see my workbook i am more than happy to share.....sorry but i am a novic

----- Rob Bovey wrote: ----

Hi Gav

Select one of the cells and run the code. This will insert the image fo
that heading. Select the next setting and run the code again. Continue fo
all additional headings. If your headings will always be located in the sam
cells you could run the code inside a loop that iterates through all o
those cell

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

headings?? There are 5 headings in total and each heading may have
correpsonding image

Gav · May 6, 2004

sorry rob, also, is there a way to have a single click event to run through the sequence (headings) rather than run it each time for each heading. The people using this arent what you would say....computer literate....

cheers!!

----- Rob Bovey wrote: ----

Hi Gav

Select one of the cells and run the code. This will insert the image fo
that heading. Select the next setting and run the code again. Continue fo
all additional headings. If your headings will always be located in the sam
cells you could run the code inside a loop that iterates through all o
those cell

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

headings?? There are 5 headings in total and each heading may have
correpsonding image

Rob Bovey · May 6, 2004

Hi Gav,

<<ok, let me see......i select the cell under the heading, click tools,
macro, select 'run' for the code. It brings the my documents window up but
when i select an image, nothing occurs.>>

I'm not sure why this would be happening. Are you sure you're clicking
the OK button after you select an image. If so, what operating system and
Excel version are you using?

<<is there a way to have a single click event to run through the sequence
(headings) rather than run it each time for each heading>>

I assume you mean you want to loop through all of the headings and
assign the pictures all at once? Will the heading cell locations ever
change? If the heading cell locations are fixed it is possible to do this.
If they will be different on different worksheets then the users will have
to select each one and run the code once for each. If they have fixed
locations, what are the cell addresses?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
Gday again.........ok, let me see......i select the cell under the

heading, click tools, macro, select 'run' for the code. It brings the my
documents window up but when i select an image, nothing occurs.

The images will always remain 1 row under the heading of the section. SO

it may be a good idea to loop. How is this done??

PS: if it is easier to see my workbook i am more than happy to

share.....sorry but i am a novice

Gav · May 7, 2004

Morning Rob,

Thanks for your help, i know this is dragging out. I am running XP and its excel 2002. I keep trying with the same result unfortunately.
Below are the headings. Data is pasted between these headings so the cell locations will shift down. The image should be in the row below the heading

Cheers!!!!

Feeder

Machine

Delivery

PECOM

Rollers

Options AUD$

* Please note that pricing is exclusive of GST

TOTAL INVESTMENT 0 Plus GST

----- Rob Bovey wrote: -----

Hi Gav,

<<ok, let me see......i select the cell under the heading, click tools,
macro, select 'run' for the code. It brings the my documents window up but
when i select an image, nothing occurs.>>

I'm not sure why this would be happening. Are you sure you're clicking
the OK button after you select an image. If so, what operating system and
Excel version are you using?

<<is there a way to have a single click event to run through the sequence
(headings) rather than run it each time for each heading>>

I assume you mean you want to loop through all of the headings and
assign the pictures all at once? Will the heading cell locations ever
change? If the heading cell locations are fixed it is possible to do this.
If they will be different on different worksheets then the users will have
to select each one and run the code once for each. If they have fixed
locations, what are the cell addresses?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
Gday again.........ok, let me see......i select the cell under the

heading, click tools, macro, select 'run' for the code. It brings the my
documents window up but when i select an image, nothing occurs.

Gav · May 7, 2004

Hi again...sorry but i trialed the code on a new book and it worked. I changed a simple part....the sheet name (!!!)...and it now says object required.......ideas???

As for the other queries, i would like a click event to run through all headings if possible??? Is it possible also to ditermine the size of the image or do i need to save the images in the folder to the size i want???

Cheers!!

----- Rob Bovey wrote: ----

Hi Gav

<<ok, let me see......i select the cell under the heading, click tools
macro, select 'run' for the code. It brings the my documents window up bu
when i select an image, nothing occurs.>

I'm not sure why this would be happening. Are you sure you're clickin
the OK button after you select an image. If so, what operating system an
Excel version are you using

<<is there a way to have a single click event to run through the sequenc
(headings) rather than run it each time for each heading>

I assume you mean you want to loop through all of the headings an
assign the pictures all at once? Will the heading cell locations eve
change? If the heading cell locations are fixed it is possible to do this
If they will be different on different worksheets then the users will hav
to select each one and run the code once for each. If they have fixe
locations, what are the cell addresses

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Gav said:
Gday again.........ok, let me see......i select the cell under th

heading, click tools, macro, select 'run' for the code. It brings the m
documents window up but when i select an image, nothing occurs

Rob Bovey · May 7, 2004

Hi Gav,

Try this variation on the code I posted previously and let me know if it
works for you.

Private Const S_OK As Long = 0
Private Const SHGFP_TYPE_CURRENT As Long = 0
Private Const CSIDL_PERSONAL As Long = 5
Private Const MAX_PATH As Long = 256

Private Declare Function SHGetFolderPathA Lib "Shell32.dll" _
(ByVal hwndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal szPath As String) As Long

Public Sub InsertImageBelowRange()
Dim objImage As Picture
Dim rngCell As Range
Dim szPath As String
Dim vFullName As Variant
Set rngCell = Selection.Offset(1, 0)
szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 1 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName <> False Then
Set objImage = ActiveSheet.Pictures.Insert(CStr(vFullName))
objImage.Top = rngCell.Top
objImage.Left = rngCell.Left
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

Private Function szGetMyDocsPath() As String
Dim szPath As String
szPath = String$(MAX_PATH, vbNullChar)
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT, _
szPath) = S_OK Then
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar))
End If
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
Morning Rob,

Thanks for your help, i know this is dragging out. I am running XP and its

excel 2002. I keep trying with the same result unfortunately.

Below are the headings. Data is pasted between these headings so the cell

locations will shift down. The image should be in the row below the heading

Gav · May 7, 2004

Excellent!! Works fine. Nearly there now!!

2 last points

1. can i have the image actuallyfill a cell....the cell directly beneath the heading rather than have it paste over data (wrap??

2. can i assign a click event that runs through all the headings as you suggested?

Thanks.....thanks....thanks....thanks!!!!!

----- Rob Bovey wrote: ----

Hi Gav

Try this variation on the code I posted previously and let me know if i
works for you

Private Const S_OK As Long =
Private Const SHGFP_TYPE_CURRENT As Long =
Private Const CSIDL_PERSONAL As Long =
Private Const MAX_PATH As Long = 25

Private Declare Function SHGetFolderPathA Lib "Shell32.dll"
(ByVal hwndOwner As Long,
ByVal nFolder As Long,
ByVal hToken As Long,
ByVal dwFlags As Long,
ByVal szPath As String) As Lon

Public Sub InsertImageBelowRange(
Dim objImage As Pictur
Dim rngCell As Rang
Dim szPath As Strin
Dim vFullName As Varian
Set rngCell = Selection.Offset(1, 0
szPath = szGetMyDocsPath() & "\
If Len(szPath) > 1 The
ChDrive szPat
ChDir szPat
vFullName = Application.GetOpenFilename(
"Image Files (*.jpg),*.jpg", , "Select an Image"
If vFullName <> False The
Set objImage = ActiveSheet.Pictures.Insert(CStr(vFullName)
objImage.Top = rngCell.To
objImage.Left = rngCell.Lef
End I
Els
MsgBox "My Documents folder not found.
End I
End Su

Private Function szGetMyDocsPath() As Strin
Dim szPath As Strin
szPath = String$(MAX_PATH, vbNullChar
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT,
szPath) = S_OK The
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar)
End I
End Functio

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Gav said:
Morning Rob

excel 2002. I keep trying with the same result unfortunately

Below are the headings. Data is pasted between these headings so the cel

locations will shift down. The image should be in the row below the headin

Rob Bovey · May 7, 2004

Hi Gav,

<<1. can i have the image actuallyfill a cell....the cell directly beneath
the heading rather than have it paste over data (wrap??)>>

I've copied a new version of just the InsertImageBelowRange procedure
that will do this. Replace the previous version of this procedure with the
one shown below. Note this may significantly distort the image depending on
what it looks like at normal size.

<<2. can i assign a click event that runs through all the headings as you
suggested??>>

I wouldn't use a click event, because that would cause the procedure to
run every time. Better to run it from a button placed on the sheet. At any
rate, yes it can be done as long as the headings are always located in the
same cells on every worksheet the user will ever need to run this for. If
this is the case, you will need to give me the specific cell addresses of
those headings in order for me to write the code.

Public Sub InsertImageBelowRange()
Dim objImage As Picture
Dim rngCell As Range
Dim szPath As String
Dim vFullName As Variant
Set rngCell = Selection.Offset(1, 0)
szPath = szGetMyDocsPath() & "\"
If Len(szPath) > 1 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName <> False Then
Set objImage = ActiveSheet.Pictures.Insert(CStr(vFullName))
objImage.Top = rngCell.Top
objImage.Left = rngCell.Left
objImage.Height = rngCell.Height
objImage.Width = rngCell.Width
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Gav said:
Excellent!! Works fine. Nearly there now!!!

2 last points:

1. can i have the image actuallyfill a cell....the cell directly beneath

the heading rather than have it paste over data (wrap??)

Dave Peterson · May 7, 2004

Psycho, er, psychic.

....or...

Dionne Warwick is one of the voices in my head!

Gav · May 7, 2004

Hello Rob, getting there. What i was hoping was for the cell to wrap according to the image size...basically accomodate the image???

Unfortunately, the cell location will vary bacause the amount of data that pastes inbetween will vary. Can we use the heading as a target????? Have the image paste 1 row below the heading, column B??

A button to run through all the headings would be great!

Cheers!!!

----- Rob Bovey wrote: ----

Hi Gav

<<1. can i have the image actuallyfill a cell....the cell directly beneat
the heading rather than have it paste over data (wrap??)>

I've copied a new version of just the InsertImageBelowRange procedur
that will do this. Replace the previous version of this procedure with th
one shown below. Note this may significantly distort the image depending o
what it looks like at normal size

<<2. can i assign a click event that runs through all the headings as yo
suggested??>

I wouldn't use a click event, because that would cause the procedure t
run every time. Better to run it from a button placed on the sheet. At an
rate, yes it can be done as long as the headings are always located in th
same cells on every worksheet the user will ever need to run this for. I
this is the case, you will need to give me the specific cell addresses o
those headings in order for me to write the code

Public Sub InsertImageBelowRange(
Dim objImage As Pictur
Dim rngCell As Rang
Dim szPath As Strin
Dim vFullName As Varian
Set rngCell = Selection.Offset(1, 0
szPath = szGetMyDocsPath() & "\
If Len(szPath) > 1 The
ChDrive szPat
ChDir szPat
vFullName = Application.GetOpenFilename(
"Image Files (*.jpg),*.jpg", , "Select an Image"
If vFullName <> False The
Set objImage = ActiveSheet.Pictures.Insert(CStr(vFullName)
objImage.Top = rngCell.To
objImage.Left = rngCell.Lef
objImage.Height = rngCell.Heigh
objImage.Width = rngCell.Widt
End I
Els
MsgBox "My Documents folder not found.
End I
End Su

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses