- Excel Version
- 2016
Here are the main features of this example:
- The form has three controls: a combo box, a text box and an image control.
- The combo box is populated from a worksheet range. Selecting a value will trigger an event which will populate the text box and the image control. VLOOKUP is used to navigate the source table.
- The pictures are retrieved from a local drive.
- If the image is small enough, like the hourglass below, all controls are visible. If the image is too large, the code shrinks the form and zooms out the image; the form caption will inform if resizing took place.
| autom(AutoRecovered).xlsm | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Item Code | Description | Path | ||
| 2 | C01 | shoe | c:\test\shoe.jpg | ||
| 3 | C02 | running shoe | c:\test\vaporfly.jpg | ||
| 4 | C03 | tuxedo | c:\test\im01.jpg | ||
| 5 | C04 | book | c:\test\book.jpg | ||
| 6 | C05 | laptop | c:\test\laptop.jpg | ||
| 7 | C06 | CD player | c:\test\cd.jpg | ||
| 8 | C07 | hourglass | c:\test\whglass.jpg | ||
| 9 | C08 | printer | c:\test\printer.jpg | ||
| 10 | C09 | watch | c:\test\wwatch.jpg | ||
| 11 | C10 | ball | c:\test\ball.jpg | ||
Sheet9 | |||||
VBA Code:
Private Sub ComboBox1_Change()
Dim img, ad$, f!, zf!
Me.TextBox1 = Evaluate("=vlookup(" & """" & Me.ComboBox1.Value & """" & _
",a2:c" & Split(Sheets("sheet9").[a2].CurrentRegion.Address, "$")(4) & ",2)")
ad = Evaluate("=vlookup(" & """" & Me.ComboBox1.Value & """" & _
",a2:c" & Split(Sheets("sheet9").[a2].CurrentRegion.Address, "$")(4) & ",3)")
Set img = Me.Image1
img.Picture = LoadPicture(ad)
With Me
With img
.Left = 0
.Top = 0
.PictureAlignment = fmPictureAlignmentTopLeft
.PictureSizeMode = fmPictureSizeModeClip
.AutoSize = True
End With
.Width = img.Width
Do While .InsideWidth <= img.Width
.Width = .Width + 3
Loop
.Height = img.Height
Do While .InsideHeight <= img.Height
.Height = .Height + 3
Loop
.Height = .Height + .ComboBox1.Height + .TextBox1.Height + 2
.ComboBox1.Left = 0
.ComboBox1.Top = img.Height + 1
.TextBox1.Left = 0
.TextBox1.Top = img.Height + .ComboBox1.Height + 1
If .Height > 500 Then ' too big
f = .Height / .Width
zf = .Height / 400
.Caption = ad & " (Resized)"
.Height = .Height / zf
.Width = .Height / f
.Zoom = .Zoom / zf
End If
End With
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = [sheet9!a2:a11].Value
Me.ComboBox1.Height = 25
Me.ComboBox1.Width = 60
Me.TextBox1.Height = 20
Me.TextBox1.Width = 60
Me.Caption = "Catalog"
Me.BorderStyle = fmBorderStyleSingle
Me.BorderColor = RGB(200, 50, 10)
End Sub
