find names value via combobox

[hsolanki]

Morning everyone

I have got the code which perfectly working. combobox1 finds the staff number and then populates names in Combobox 1 & 5 however i would like to change this from a staff number to names find

below is the code whereby it finds it using the staff number. the staff number is in column A & names in column B

Private Sub ComboBox1_Change()

    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), ThisWorkbook.Sheets("Monthly Summary").Range("A:B"), 2, 0)
        Me.TextBox6.Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), ThisWorkbook.Sheets("Monthly Summary").Range("A:C"), 3, 0)
        
    Else
        Me.TextBox1.Value = ""
    End If

End Sub

 

[Legacy 143009]

Private Sub ComboBox1_Change()
    With ThisWorkbook.Sheets("Monthly Summary")
    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = WorksheetFunction.Vlookup(Me.ComboBox1.Value, .Range("A:C"), 2, 0)
        Me.TextBox6.Value = WorksheetFunction.Vlookup(Me.ComboBox1.Value, .Range("A:C"), 3, 0)
    Else
        Me.TextBox1.Value = ""
        Me.TextBox6.Value = ""
    End If
    End With
End Sub

 

[hsolanki]

Hi I get an error when I tried the suggested code

 

[Legacy 143009]

I hope this will solve your problem:

Private Sub ComboBox1_Change()
    With ThisWorkbook.Sheets("Monthly Summary")
    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = WorksheetFunction.Vlookup(CLng(Me.ComboBox1.Value), .Range("A:C"), 2, 0)
        Me.TextBox6.Value = WorksheetFunction.Vlookup(CLng(Me.ComboBox1.Value), .Range("A:C"), 3, 0)
    Else
        Me.TextBox1.Value = ""
        Me.TextBox6.Value = ""
    End If
    End With
End Sub

 

[hsolanki]

Hi Flash sorry, this is the final code whereby it works now

Private Sub ComboBox1_Change()

  With ThisWorkbook.Sheets("Monthly Summary")
    If Me.ComboBox1.Value <> "" Then
        Me.TextBox1.Value = .Cells(WorksheetFunction.Match(Me.ComboBox1.Value, .Range("B:B"), 0), 1).Value
        Me.TextBox6.Value = WorksheetFunction.VLookup(Me.ComboBox1.Value, .Range("B:C"), 2, 0) 
    Else
        Me.TextBox1.Value = ""
    End If
    End With

End Sub

 

[Legacy 143009]

Perfect! Thanks for the follow up