How to Place Relevant Image on User Form based on Data filled in Text Boxes

How to place a relevant image on the user-form based on data filled in the text Boxes automatically using VBA. Last time we learnt how to fill the text boxes with data based on selection in combo-box. Now we learn to place an image on the user-form based on the data in the text-boxes. In this manner we can display image of students, employees, items, etc. Watch the video:

 

Watch the video on YouTube.

 

Here’s the complete VBA code:

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub Image1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets(“Sheet1”)
LastRow = ws.Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Me.ComboBox1.AddItem ws.Cells(i, “A”).Value
Next i
End Sub

Private Sub ComboBox1_Change()
Dim fpath As String
fpath = “C:\student-pics”
‘fpath = ThisWorkbook.Path
If Right(fpath, 1) <> “\” Then
fpath = fpath & “\”
End If

Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets(“Sheet1”)
LastRow = ws.Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.ComboBox1.Value) = ws.Cells(i, “A”) Then
MsgBox Me.ComboBox1.Value
Me.TextBox1 = ws.Cells(i, “B”).Value
Me.TextBox2 = ws.Cells(i, “C”).Value
Me.TextBox3 = ws.Cells(i, “D”).Value
Me.TextBox4 = ws.Cells(i, “E”).Value
Me.Image1.Picture = LoadPicture(fpath & Me.TextBox1.Text & Me.TextBox2.Text & “.jpg”)
End If

Next i

End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “How to Place Relevant Image on User Form based on Data filled in Text Boxes”

  1. I want to save picture in excel cell
    But i want to use image control tool in user form
    What will be the procedure and vba code accordingly

  2. i made an excel file like it is shown in the video, and copy paste all the VBA commands as it is shown above, but still i cant run my user form, it gives me “run-time error 9” “subscript out of range”. Can you please sent me that example file to my email?
    Many thanks

  3. Can you please send me that example workbook please.
    I did it as it is shown in the video and copy paste the commands above but still gives me “run-time error 9”
    I will be so thankful if you send me that example workbook
    Many thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.