Picture resizing

Hi all and thanks for your help thus far!

here's what i want to do:
1) take a picture of a range of a worksheet
2) put that picture in a user form
3) resize the picture so that it fits in the form
4) and with #3 done be sure that the picture is still readable

here's what i've done

#1,2,and 3

#4 is what i'm having problems with.

i can resize the picture, however it is no longer readable.
i have tried with a frame and using the scroll bars but that will not work
for my end users.

the code below uses the PastePicture sub (By Stephen Bullen) suggested to me.

<begin vb code>

'need to do this o/w copy doesn't get anything
Application.ScreenUpdating = True

Dim fName As String, filePath As String
Dim lPicType As Long


fName = "" & workOrderNumber & ".xls"
filePath = "./orders/" & fName

Workbooks.Open filePath

' using this copy with xlPicture doesn't get anything either (pastes
empty)
Worksheets("Work Order").Range("b12:l51").CopyPicture xlScreen, xlBitmap

Image2.AutoSize = True

lPicType = IIf(obMetafile, xlPicture, xlBitmap)
Set Image2.Picture = PastePicture(lPicType)
Image2.PictureSizeMode = fmPictureSizeModeZoom

Image2.Height = 227
Image2.Width = 285

Workbooks(fName).Close SaveChanges:=True
ExistingWO.Show
<end vb code>

(the height and width were chosen as 1/3 the size of the image)

any thoughts on where to go from here to get the range scaled down properly
and have it readable?

A lot depends on factors such as your screen resolution, what is in the
original "picture", the fonts used, etc. But I think the basic problem is
that there are limits to what you can do in Excel Image boxes as far as
scaling, and unless it scales just right the picture will get distorted -
remember that pictures have to be converted into pixels on the screen, and if
the pixels in the new scale don't line up exactly, they get jagged or the
picture gets distorted. More sophisticated image editing software can do a
better job of scaling pictures and antialiasing (smoothing the pixels), but
you would then need to be able to automate that from Excel, and even then it
is often hard to know what settings will give a "readable" result without
some user intervention, I think. "Readable" is a subjective matter that does
not lend itself well to a "coded" solution.

"Gixxer_J_97" wrote:

> Hi all and thanks for your help thus far!
>
> here's what i want to do:
> 1) take a picture of a range of a worksheet
> 2) put that picture in a user form
> 3) resize the picture so that it fits in the form
> 4) and with #3 done be sure that the picture is still readable
>
> here's what i've done
>
> #1,2,and 3
>
> #4 is what i'm having problems with.
>
> i can resize the picture, however it is no longer readable.
> i have tried with a frame and using the scroll bars but that will not work
> for my end users.
>
> the code below uses the PastePicture sub (By Stephen Bullen) suggested to me.
>
> <begin vb code>
>
> 'need to do this o/w copy doesn't get anything
> Application.ScreenUpdating = True
>
> Dim fName As String, filePath As String
> Dim lPicType As Long
>
>
> fName = "" & workOrderNumber & ".xls"
> filePath = "./orders/" & fName
>
> Workbooks.Open filePath
>
> ' using this copy with xlPicture doesn't get anything either (pastes
> empty)
> Worksheets("Work Order").Range("b12:l51").CopyPicture xlScreen, xlBitmap
>
> Image2.AutoSize = True
>
> lPicType = IIf(obMetafile, xlPicture, xlBitmap)
> Set Image2.Picture = PastePicture(lPicType)
> Image2.PictureSizeMode = fmPictureSizeModeZoom
>
> Image2.Height = 227
> Image2.Width = 285
>
> Workbooks(fName).Close SaveChanges:=True
> ExistingWO.Show
> <end vb code>
>
> (the height and width were chosen as 1/3 the size of the image)
>
> any thoughts on where to go from here to get the range scaled down properly
> and have it readable?

thats exactly the problem that i've been running into. it would be better if
i could take the range i want from the worksheet and have that range inserted
into the user form (ie the userform would be editable). that would solve my
problem.

and again - that's where i'm stuck getting the range into the user form
without making a bunch of labels/text boxes and selecting all of the values.



"K Dales" wrote:

> A lot depends on factors such as your screen resolution, what is in the
> original "picture", the fonts used, etc. But I think the basic problem is
> that there are limits to what you can do in Excel Image boxes as far as
> scaling, and unless it scales just right the picture will get distorted -
> remember that pictures have to be converted into pixels on the screen, and if
> the pixels in the new scale don't line up exactly, they get jagged or the
> picture gets distorted. More sophisticated image editing software can do a
> better job of scaling pictures and antialiasing (smoothing the pixels), but
> you would then need to be able to automate that from Excel, and even then it
> is often hard to know what settings will give a "readable" result without
> some user intervention, I think. "Readable" is a subjective matter that does
> not lend itself well to a "coded" solution.
>
> "Gixxer_J_97" wrote:
>
> > Hi all and thanks for your help thus far!
> >
> > here's what i want to do:
> > 1) take a picture of a range of a worksheet
> > 2) put that picture in a user form
> > 3) resize the picture so that it fits in the form
> > 4) and with #3 done be sure that the picture is still readable
> >
> > here's what i've done
> >
> > #1,2,and 3
> >
> > #4 is what i'm having problems with.
> >
> > i can resize the picture, however it is no longer readable.
> > i have tried with a frame and using the scroll bars but that will not work
> > for my end users.
> >
> > the code below uses the PastePicture sub (By Stephen Bullen) suggested to me.
> >
> > <begin vb code>
> >
> > 'need to do this o/w copy doesn't get anything
> > Application.ScreenUpdating = True
> >
> > Dim fName As String, filePath As String
> > Dim lPicType As Long
> >
> >
> > fName = "" & workOrderNumber & ".xls"
> > filePath = "./orders/" & fName
> >
> > Workbooks.Open filePath
> >
> > ' using this copy with xlPicture doesn't get anything either (pastes
> > empty)
> > Worksheets("Work Order").Range("b12:l51").CopyPicture xlScreen, xlBitmap
> >
> > Image2.AutoSize = True
> >
> > lPicType = IIf(obMetafile, xlPicture, xlBitmap)
> > Set Image2.Picture = PastePicture(lPicType)
> > Image2.PictureSizeMode = fmPictureSizeModeZoom
> >
> > Image2.Height = 227
> > Image2.Width = 285
> >
> > Workbooks(fName).Close SaveChanges:=True
> > ExistingWO.Show
> > <end vb code>
> >
> > (the height and width were chosen as 1/3 the size of the image)
> >
> > any thoughts on where to go from here to get the range scaled down properly
> > and have it readable?