fill combobox selection based on a selection from the first combobox

[Monza]

Hi,

I have a client database sorted into columns which are named defined i.e Companyname, Firstname, Lastname, Mobile, Email, etc

I have created a user form for the operator to enter in a search for company name and from this search a list of the corresponding employees will appear. From this list, I wish the operator to than be able to select the relevant employee and then all thier details will appear (mobile, address, email, position etc)

I have created a combobox which enables you to type in the company name and it auto spells out and shows list, from there I need the next combo box to show relatiive employee.

Thanks

[jaslake]

Hi Monza

Welcome to the Forum!

This Code is in the attached and appears to do as you require

Option Explicit

Private Sub ComboBox1_GotFocus()
    Me.ComboBox2.Clear
End Sub

Private Sub ComboBox1_LostFocus()
    Call ComboBox1_Click
End Sub

Private Sub ComboBox1_Click()
    Dim ws As Worksheet
    Dim Rng As Range, Cel As Range
    Dim LR As Long

    Me.ComboBox2.Clear
    Set ws = Sheets("database")
    With ws
        .AutoFilterMode = False
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row

        .Range("A1:Z" & LR).AutoFilter Field:=1, Criteria1:= _
                Me.ComboBox1.Value
        Set Rng = .Range(("B2"), .Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
        For Each Cel In Rng
            Me.ComboBox2.AddItem Cel.Value
        Next Cel
    End With
    Me.ComboBox2.Value = ""
    Me.ComboBox2.Activate
End Sub

Private Sub ComboBox2_Click()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim Rng As Range, c As Range
    Set ws1 = Sheets("database")
    Set ws = Sheets("mainform")
    With ws1
        Set Rng = .Range(("B2"), .Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
        Set c = Rng.Find(Me.ComboBox2.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            ws.Range("D9").Value = c.Offset(0, 1).Value
            ws.Range("F9").Value = c.Offset(0, 2).Value
            ws.Range("D11").Value = c.Offset(0, 3).Value
            ws.Range("D13").Value = c.Offset(0, -1).Value
            ws.Range("D15").Value = c.Offset(0, 7).Value
            ws.Range("D17").Value = c.Offset(0, 8).Value
            ws.Range("G17").Value = c.Offset(0, 9).Value
            ws.Range("D19").Value = c.Offset(0, 10).Value
            ws.Range("J11").Value = c.Offset(0, 5).Value
            ws.Range("J13").Value = c.Offset(0, 6).Value
            ws.Range("J15").Value = c.Offset(0, 4).Value
            ws.Range("D21").Value = c.Offset(0, 11).Value
        End If
        .AutoFilterMode = False
    End With
    Me.ComboBox2.Value = ""
End Sub

[Monza]

Thank you so much for your amazing assistance and prompt reply, your answer works perfectly! genuis

[jaslake]

You're welcome...glad I could help...Thanks for the Rep...

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save