How to Add or Change an Employee Picture on a UserForm

Video Tutorial Contents

SET UP SPREAD SHEET
0:37 ]- 1. Insert Column for File Path
1:01 ]- 2. Make NAMED RANGE for List Box display

1:26 ]- USER FORM SET UP (Controls & Property Sheet settings)
1:32 ]- Command Buttons
1:35 ]- Text Boxes - invisible
1:42 ]- Image Control
1:49 ]- ListBox

LIST BOX PICTURE CHANGE
2:14 ]- ListBox_Click() - "Change UserForm Picture on List Box Mouse Click CODE"
2:29 ]- List Box Explanation
2:46 ]- LIST Command Explanation
3:11 ]- ListIndex Command Explanation
3:23 ]- ListBox_Click() - Step by Step VBA Code Explanation

4:26 ]- ASSIGN PICTURE CHANGE PROCEEDURE TO LIST BOX ON USER FORM
4:49 ]- How to Make a List Box Column Invisible

5:18 ]- BUTTON CLICK OPEN DIALOGUE BOX PROCEEDURE
5:26 ]- variable declarations
5:31 ]- Button Click - Open Dialogue Box - Step by Step VBA Code Explanation
6:09 ]- Complete Add Picture Proceedure Step by Step explanation

7:10 ]- Assign Open Dialogue Box Proceedure to Command Button on UserForm
7:27 ]- Closing Remarks

VBA Programing Code used in Tutorial - [ YES Comments added to code ]


UserForm & Control Names

UserForm: ................. frm_EmployInfo

Command Buttons:.btn_AddPicture

..................................btn_OK

Text Boxes:...............txt_FirstName

.................................txt_LastName

Image Control: .......img_EmployPict

List Box:.................... lst_Employee_info



CODE IN UserForm - with Comments

Option Explicit
Private Sub btn_AddPicture_Click()   '<-[ Open Dialogue Box Button ]
    EmpoyInfo_ADDPict  '<-[ Procedure in Module
 End Sub
Private Sub lst_Employee_info_Click()  '<-[ List box Click Event Code
    ListBox_Click  '<-[ Procedure in Module
 End Sub
 Private Sub btn_OK_Click()  '<-[ Cancel Button code
    Unload frm_EmployInfo
 End Sub  


VBA Code in Module

Option Explicit


'<-[ Search & Row Variables ]
    Dim FirstName As String, LastName As String
    Dim DataRow As Integer
    Dim LisRow As Integer

 '<-[ Open Dialogue Box Variables ]
    Dim PictureName As Variant
    Dim filename As Variant
    Dim PictureDialog As Office.FileDialog
Sub Menu_EmployInfo_Show() '<-[ Brings UserForm on SpreadSheet Menu Button Click ]
   frm_EmployInfo.Show
End Sub
Sub ListBox_Click() '<-[ Loads & Changes UserForm Control Info ]
 On Error Resume Next  '<-[ Error correction code

'<-[ Load Variables ]
   LisRow = frm_EmployInfo.lst_Employee_info.ListIndex        '<-[ Loads ListBox row number into Variable
   PictureName = frm_EmployInfo.lst_Employee_info.List(LisRow, 3)  '<-[ Loads Path & File name into Variable

'<-[ Loads UserForm Controls with current ListBox row Info ]
   With frm_EmployInfo
      .txt_LastName.Value = .lst_Employee_info.List(LisRow, 0)  '<-[ Loads Last Name info into Text Box
      .txt_FirstName.Value = .lst_Employee_info.List(LisRow, 1)  '<-[ Loads First Name info into Text Box
      .img_EmployPict.Picture = LoadPicture(PictureName)         '<-[ Loads Path & File Name into Picture Control
   End With
End Sub
Sub EmpoyInfo_ADDPict()  '<-[ Brings up Open Dialogue Box & puts path & Filename into SpreadSheet
   On Error Resume Next  '<-[ Error correction code ]
   Application.ScreenUpdating = False  '<-[ Turn OFF Screen Updating

'<-[ Load Variables ]
LisRow = frm_EmployInfo.lst_Employee_info.ListIndex
LastName = frm_EmployInfo.lst_Employee_info.List(LisRow, 0)
FirstName = frm_EmployInfo.lst_Employee_info.List(LisRow, 1)
DataRow = 3

Sheets("MainData").Select  '<-[ Change Sheets to Main Database WorkSheet
 Range("A3").Select           '<-[ Select Cell to start Do Loop'<-[ Search for Matchiing Last & First name aquired from List Bo

Do Until Cells(DataRow, 1).Value = ""    '<-[ Start Loop & Continue Loop until end of Last Name column
    If Cells(DataRow, 1).Value = LastName And Cells(DataRow, 2).Value = FirstName Then
       Cells(DataRow, 4).Select               '<-[ Select SpreadSheet Cell for Image path & file Name
       Exit Do
    Else
       DataRow = DataRow + 1  '<-[ Counter -> Sends loop to next lower Row number
    End If
Loop'<-[ Bring up Open Dialogue box ]

Set PictureDialog = Application.FileDialog(msoFileDialogFilePicker)

With PictureDialog                     '<-[ Modifies the Dialogue box
     .AllowMultiSelect = False         '<-[ Only Allow one item to be selected
     .Filters.Clear                         '<-[ Erase ALL file types shown in dialogue box
     .Filters.Add "Images", "*.bmp; *.gif; *.jpg; *.jpeg; *.png"  '<-[ Add Custom File type filters
     If .Show = -1 Then                '<-[ If TRUE, file type is showing in dialogue box
        For Each filename In .SelectedItems '<-[ get complete path & file name of selected file
          PictureName = filename             '<-[ When OK button clicked, put path & file name into Variable
        Next
     End If
End With

'<-[ Load Spread Sheet Cell with path & File name ]
 Cells(DataRow, 4).Value = PictureName  '<-[ copy path & file name from variable into cell on spreadsheet
 Columns("C:C").EntireColumn.AutoFit     '<-[ Re-Size columns for New path & file name size

 Sheets("DashBoard").Select  '<-[ Change Sheets back to Dashboard (starting) WorkSheet

'<-[ Load Image Control with new path & file name ]
 frm_EmployInfo.img_EmployPict.Picture = LoadPicture(PictureName)  '<-[ Copy path & file Name from variable to Image control
   Application.ScreenUpdating = True  '<-[ Turn ON Screen Updating ]
End Sub