Excel's UserForm does not have a PictureBox control available, but it does
have an Image control which is what we will use. Here is the subroutine
which will do all the work (see rest of message after the code)...
Private Sub SetImageBoxSize(ImageBox As Control, Optional _
ImageReductionAmount As Long = 0)
Dim BorderAdjustment As Long
Dim ParentRatio As Single
Dim PictureRatio As Single
Dim ContainerWidth As Single
Dim ContainerHeight As Single
With ImageBox
.Visible = False
.AutoSize = True
PictureRatio = .Width / .Height
If TypeName(.Parent) = Me.Name Then
With .Parent
ContainerWidth = .InsideWidth
ContainerHeight = .InsideHeight
BorderAdjustment = 0
End With
ElseIf TypeName(.Parent) = "Frame" Then
ContainerWidth = .Parent.Width
ContainerHeight = .Parent.Height
BorderAdjustment = 1
Else
Exit Sub
End If
ParentRatio = ContainerWidth / ContainerHeight
If ParentRatio < PictureRatio Then
.Width = ContainerWidth - ImageReductionAmount - BorderAdjustment
.Height = .Width / PictureRatio
Else
.Height = ContainerHeight - ImageReductionAmount - BorderAdjustment
.Width = .Height * PictureRatio
End If
.PictureSizeMode = fmPictureSizeModeStretch
.Move (ContainerWidth - .Width) \ 2, _
(ContainerHeight - .Height) \ 2
.Visible = True
End With
End Sub
Okay, here is a demo project to show how to use it. Insert a new UserForm
and place a Frame control, an Image control and a CommandButton on the form.
Size the Frame control to fill most of the UserForm (leave space for the
CommandButton), place the Image control inside the Frame (the subroutine
will proportionally resize the Image control to fit the Frame) and place the
CommandButton off to the side of the Frame somewhere. Set the following
properties for the Frame control (nothing special needs to be done for the
other controls)...
BorderStyle: 1-fmBorderStyleSingle
Caption: <delete> 'there will be no caption
Okay, after you copy/paste the above subroutine into the UserForm's code
window, copy paste this CommandButton event code in there also...
Private Sub CommandButton1_Click()
Dim FileName As String
FileName = Application.GetOpenFilename(FileFilter:="Images(*.jpg),*.jpg")
Image1.Picture = LoadPicture(FileName)
SetImageBoxSize Image1
End Sub
That's it. Run the UserForm, click the CommandButton and select an image to
display. Then click the CommandButton again and select a different image to
display. Each time you do that, the Image will be sized to fit the Frame
control while maintaining its aspect ratio.
Okay, you may have noticed that the SetImageBoxSize subroutine has an
Optional ImageReductionAmount argument. With this argument, you can create a
"border" area between the picture and the Frame's border line. The argument
specifies the minimum number of pixels to leave between the picture and the
Frame's border (minimum because the aspect ratio of the picture and that of
the Frame will probably differ). So, if you used this...
SetImageBoxSize Image1, 15
in the CommandButton's Click event above, then the picture would be reduced
in size (while still maintaining its aspect ratio) so that 15 pixels minimum
separated it from the Frame's border.
One more thing. You don't have to use a Frame control... you can use the
UserForm instead. If you remove the Frame control from the UserForm in the
demo project above and run the UserForm, the image control will size itself
to the UserForm's inside area (you can still specify a value for the
Optional ImageReductionAmount argument if you want and it will maintain that
minimum distance from the edges of the UserForm).
--
Rick (MVP - Excel)
You are using aWebBrowser, but your code shows you are displaying a
picture
from a local hard drive... is that the only source of your pictures to be
displayed or will you actually need to display pictures from the web as
well? If you only have to display pictures from an attached drive, I have
an
alternative approach for you to use. I'm not sure how easily it can be
adapted to the a web source for your picture though.
--
Rick (MVP - Excel)
- Show quoted text -
Ok sure, that will be fine, I guess I could just add a picture box,
and link it upto the file, but what would the code be for that?
