• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
Worf

Dynamic user form images with VBA

Excel Version
  1. 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
ABC
1Item CodeDescriptionPath
2C01shoec:\test\shoe.jpg
3C02running shoec:\test\vaporfly.jpg
4C03tuxedoc:\test\im01.jpg
5C04bookc:\test\book.jpg
6C05laptopc:\test\laptop.jpg
7C06CD playerc:\test\cd.jpg
8C07hourglassc:\test\whglass.jpg
9C08printerc:\test\printer.jpg
10C09watchc:\test\wwatch.jpg
11C10ballc:\test\ball.jpg
Sheet9


3_sec.PNG


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
Author
Worf
Views
3,831
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Worf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top