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

Leave a Reply

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