Re: External images

From: Rob Bovey (Rob_Bovey_at_msn.com)
Date: 05/07/04 

Date: Thu, 6 May 2004 23:52:15 -0700

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
work***. 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" <anonymous@discussions.microsoft.com> wrote in message
news:C4B418A4-F630-4DFD-B1DF-782583DC4835@microsoft.com...
> 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 ***.
At any
>      rate, yes it can be done as long as the headings are always located
in the
>      same cells on every work*** 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 =
Active***.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" <anonymous@discussions.microsoft.com> wrote in message
>      news:65AC135E-C9CA-4147-90DE-14B20508CDB3@microsoft.com...
>      > 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!!!!!!
>
>
>
Loading