Results 1 to 13 of 13

Item in ListBox (search) to populate UserForm with values

Threaded View

  1. #1
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    Item in ListBox (search) to populate UserForm with values

    Hi all,

    I would appreciate some help (again)!

    I would like to combine 2 functions.

    I have a Search feature that updates a ListBox with possible matches as you type. I would like the following to happen when you dbl click a record in the ListBox: It populates the UserForm with the data of that specific row in your sheet.

    Here is my code for the Search feature (works):

    Private Sub TextBox1_Change()
        Dim rng As Range, e
        With Me
            .ListBox3.Clear
            If Len(.TextBox1.Value) Then
                For Each e In Sheets("DataSheet - Fitness").Cells(1).CurrentRegion.Columns(1).Offset(1).Value
                    If (e <> "") * (e Like "*" & .TextBox1.Value & "*") Then
                        .ListBox3.AddItem e
                    End If
                Next
                With .ListBox3
                    If .ListCount > 0 Then .ListIndex = 0
                End With
            End If
        End With
    End Sub
    Here is my code for dbl click of item in the ListBox that is to populate my UserForm (doesn't work):

    Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim idx As Long
    
        'idx = cmbDERS.ListIndex
    
        If idx <> -1 Then
            With Worksheets("DataSheet - Fitness")
      
     GUID.Text = .Cells(idx + 2, -5).Value
     txtCompany.Text = .Cells(idx + 2, -4).Value
     txtDepartment.Text = .Cells(idx + 2, -3).Value
     txtSite.Text = .Cells(idx + 2, -2).Value
     txtDate.Text = .Cells(idx + 2, -1).Value
     txtID.Text = .Cells(idx + 2, 0).Value
     txtName.Text = .Cells(idx + 2, 1).Value
     txtSurname.Text = .Cells(idx + 2, 2).Value
     txtOccupation.Text = .Cells(idx + 2, 3).Value
     cboMedical.Text = .Cells(idx + 2, 4).Value
     txtOtherMedical.Text = .Cells(idx + 2, 5).Value
     cboOREP.Text = .Cells(idx + 2, 6).Value
     cboJob.Text = .Cells(idx + 2, 7).Value
     cboFull.Text = .Cells(idx + 2, 8).Value
     cboShort.Text = .Cells(idx + 2, 9).Value
     txtRestrictions.Text = .Cells(idx + 2, 10).Value
     txtComments.Text = .Cells(idx + 2, 11).Value
     txtEmployee.Text = .Cells(idx + 2, 12).Value
     txtExpiry.Text = .Cells(idx + 2, 13).Value
        
    'Tests Performed
    cbxPhysical.Value = .Cells(idx + 2, 14).Value
    cbxUrine.Value = .Cells(idx + 2, 15).Value
    cbxBP.Value = .Cells(idx + 2, 16).Value
    cbxHeight.Value = .Cells(idx + 2, 17).Value
    cbxWeight.Value = .Cells(idx + 2, 18).Value
    cbxSpiro.Value = .Cells(idx + 2, 19).Value
    cbxAudio.Value = .Cells(idx + 2, 20).Value
    cbxCXR.Value = .Cells(idx + 2, 21).Value
    cbxEndurance.Value = .Cells(idx + 2, 22).Value
    cbxDrug.Value = .Cells(idx + 2, 23).Value
    cbxAlcohol.Value = .Cells(idx + 2, 24).Value
    cbxBlood.Value = .Cells(idx + 2, 25).Value
    cbxOther.Value = .Cells(idx + 2, 26).Value
    txtList.Text = .Cells(idx + 2, 27).Value
        
    'Fitness Classification
    optFit.Value = .Cells(idx + 2, 28).Value
    optRestricted.Value = .Cells(idx + 2, 29).Value
    optUnfit.Value = .Cells(idx + 2, 30).Value
    optTemp.Value = .Cells(idx + 2, 31).Value
    cboChronic.Text = .Cells(idx + 2, 32).Value
        
    'Person Classification
     txtVcat.Text = .Cells(idx + 2, 33).Value
     txtHcat.Text = .Cells(idx + 2, 34).Value
     txtLcat.Text = .Cells(idx + 2, 35).Value
     txtPcat.Text = .Cells(idx + 2, 36).Value
     txtBMcat.Text = .Cells(idx + 2, 37).Value
    
            End With
        End If
    
    End Sub
    Thank you all!
    Attached Files Attached Files
    Last edited by onmyway; 08-02-2013 at 06:29 AM. Reason: Made changes

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populate a Listbox in a UserForm based on a Search Result
    By clapforthewolfman in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 07-17-2013, 07:53 PM
  2. VBA - Populate Listbox based on value selected in another ListBox (On Userform)
    By raaboo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-12-2012, 11:18 AM
  3. Populate a Userform from a selection on a popup search listbox
    By ahmadassaad in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-16-2012, 09:00 AM
  4. Userform populate listbox with search from multiple textboxes
    By chendysworld in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-12-2012, 11:12 AM
  5. Populate ListBox Based On Item Chosen In Another ListBox
    By davemojo82 in forum Excel General
    Replies: 1
    Last Post: 08-04-2009, 08:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1