• 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
1,523
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Worf

This Week's Hot Topics

  • separating multiple Proper names (uppercase letters) from a list
    I have a list of proper names that only has spaces between their first and last names. I need a formula that can separate these names into...
  • 'for' Loop
    Hello guys, I am trying to do something new. With the help of a VBA code, in every case, last 2 Values of last 2 cells of columns D-15 to D-28 to...
  • Open specific pdf in folder with vba
    Hi, Below is the code in use , unable to find rich icon to put code inside using a mobile. The code in use is shown below. Everything works as it...
  • What is wrong with this For Loop code?
    I am trying to loop through each cell in Column U from U4 to last row to check if the percentage in the cell is greater than -.050%. In a...
  • Data to match
    Hi there, I have created 2 worksheets with data. First is all materials used and second has the bottle type used for each of material. How can I...
  • Shifting Columns
    Hi Dear Community, I'm trying to find the best way to Shift (Cut - Paste) all the information below and to the right of the "Worker" cell in VBA...

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