Excel

Listbox-Show Picture on Click-Select Data on Double-Click

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

lucas

Description:

Open the form and click on a persons name and their picture appears in the image control. If you double-click on their name the form closes and their data is selected. 

Discussion:

You have an employee database in excel and your looking for a certain person but your not sure of their name. If you can see a picture of them you might be able to recognize them. 

Code:

instructions for use

			

Put this code In a standard Module: Option Explicit Sub ShowMyUserForm() MyUserForm.Show End Sub Put this code In the module For your userform: Option Explicit 'When a name in the listbox is double-clicked, select the corresponding row Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim Employee As Variant Dim Name As String Dim firstaddress As String If IsNull(ListBox1.Value) Then Exit Sub Employee = Empty 'If you add more than 500 names you will need to increase this With ActiveSheet.Range("a1:a500") Name = ListBox1.Value Set Employee = .Find(what:=Name, LookIn:=xlValues) If Not Employee Is Nothing Then Employee.Rows.EntireRow.Select Else Exit Sub End With 'closes the form when you double-click on a name Unload Me Set Employee = Nothing End Sub Private Sub UserForm_activate() Dim MyList(9, 3) 'as array type Dim R As Integer 'The list box contains 1 data column. 'You can configure the number of columns, their width and height below 'as well as change the text in the ControlTipText of the listbox Application.ShowToolTips = True With ListBox1 .ColumnCount = 1 .ColumnWidths = 75 .Width = 230 .Height = 110 .ControlTipText = "Click the Name, Job, or ID you're after" End With 'Define the list and where it's obtained from (Columns A, D, G in this example) With ActiveSheet 'MyList (Row{0 to 9}, Column{0 to 2}) = the ranges given For R = 0 To 9 MyList(R, 0) = .Range("A" & R + 1) MyList(R, 1) = .Range("D" & R + 1) MyList(R, 2) = .Range("G" & R + 1) Next R End With 'populate the list box ListBox1.List = MyList End Sub 'This sub uses the list box to select the name on the spreadsheet 'and display their picture if one is found Private Sub listBox1_Click() Dim EmpFound As Range Dim fPath As String 'selects the range to look for a name. You have to have a range 'named myName set up on column A. Go to insert-name-define to 'see how this one is set up. With Range("myName") Set EmpFound = .Find(ListBox1.Value) On Error Resume Next If EmpFound Is Nothing Then Image1.Picture = LoadPicture(fPath & "nopic.gif") Else With EmpFound 'Look in the directory where this workbook is located. fPath = ThisWorkbook.Path & "\" On Error Resume Next 'If a matching picture is found then display it. Image1.Picture = LoadPicture(fPath & "\" & ListBox1.Value & ".jpg") 'If No picture found then display the default picture. If Err = 0 Then Exit Sub Image1.Picture = LoadPicture(fPath & "nopic.gif") End With End If End With Set EmpFound = Nothing End Sub

How to use:

  1. Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
  2. On the toolbar of the Visual Basic Editor, go to insert - form
  3. It needs to be named UserForm1 or be changed in the code to match what you use to show the form in the standard module.
  4. Onto your form drag one listbox and one image control from the toolbox.
  5. They should be named ListBox1 and Image1 by default, if not rename them or change the names in the code to match from ListBox1 and Image1 to the names of your listbox and image control.
  6. In the project explorer right click on the form you have created and left click on view code.
  7. In the module pane for the form paste the code above.
  8. Go to the main menu and click on insert-module.
  9. Into this code module paste the code above for the standard module.
  10. Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
  11. The pictures of the employees need to be resized to approximatly 100X100pixels and they need to be named the same as the employees name in the Excel file. So the Bart Simpson picture needs to be named Bart Simpson.jpg spaces and all. They also must accompany the excel file. The excel file will look in the same directory it is in for the pictures.
 

Test the code:

  1. On the main menu go to tools-macro-macros.
  2. In the dialog window select ListBoxForm and then click run.
  3. When the Listbox appears just click on a name and the picture of the employee will appear in the image control if a picture exists. If no picture exists for that employee a default picture will appear. If you double click on the employee name the row containing their data will be selected.
 

Sample File:

click listbox-show picture.zip 25.31KB 

Approved by mdmackillop


This entry has been viewed 345 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express