Hi Gav,
<<What i was hoping was for the cell to wrap according to the image
size...basically accomodate the image
You can do this for the row height by adding the following line of code
directly below the lines that set the top and left of the image:
rngCell.RowHeight = objImage.Height
Column width is not so simple because it does not have a consistent unit of
measurement. ColumnWidth is measured in terms of the font being used in that
column, so it's difficult to convert to the units of the image width. At any
rate, you can only have one column width for a column and you're stacking
multiple images in the same column, so I'm not so sure how well this would
work anyway.
<<Unfortunately, the cell location will vary bacause the amount of data that
pastes inbetween will vary. Can we use the heading as a target
Yes, that is how the code is constructed now. Unfortunately, it means
that you will have to select each heading cell and run it for each heading.
To add a button to run this code, right-click over the toolbar area and
display the Forms toolbar. The Button object will be on the second row,
right side. Click it and then use your mouse to drag out a button on the
worksheet. The Assign Macro dialog will pop up as soon as you finish. Select
the InsertImageBelowRange macro and click OK. Edit the button text to say
whatever you want. Now when you click the button the procedure will run.
--
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" wrote in message
...
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
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" wrote in message
...
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!!!!!!