search with Interdependent combobox

[Vishwanath Korgaonkar]

H
I have two combobox one for emp Id(combobox12) and one for emp name(combobox13).The values are added in combobox with change event of combobox1. if I select name from combobox13 the it display corresponding Emp id in combobox12 my code is as follows

Private Sub ComboBox13_Change()

Dim i As Long, lastrow As Long
Dim sh As Worksheet
With ActiveWorkbook.Sheets("Other_Details")
Set sh = ActiveWorkbook.Sheets("Other_Details")
lastrow = sh.Range("AH" & Rows.Count).End(xlUp).Row
For i = 4 To lastrow

If sh.Cells(i, "AH").Value = Me.ComboBox13.Value Then
Me.ComboBox12.Value = sh.Cells(i, "AG").Value

End If
Next
End With
End Sub

But if I use the code other way round it does not work.for example if I select the employee ID it does not display employee name nor it gives any error my code is as follows

Private Sub ComboBox12_Change()

Dim i As Long, lastrow As Long
Dim sh As Worksheet
With ActiveWorkbook.Sheets("Other_Details")
Set sh = ActiveWorkbook.Sheets("Other_Details")
lastrow = sh.Range("AG" & Rows.Count).End(xlUp).Row
For i = 4 To lastrow

If sh.Cells(i, "AG").Value = Me.ComboBox12.Value Then
Me.ComboBox13.Value = sh.Cells(i, "AH").Value

End If
Next
End With

the data range is in whorksheet "Other_Details" cells AG:AH
My same logic works for 2 others comboxes in same userform but I cannot figure out what is gone wrong?
can any one help?

 

[DanteAmor]

Try this option:

  Dim sh As Worksheet   'At the beginning of all the code
  
Private Sub ComboBox12_Change()
  If ComboBox12.ListIndex = -1 Then Exit Sub
  ComboBox13.Value = sh.Range("AH" & ComboBox12.ListIndex + 4).Value
End Sub
Private Sub ComboBox13_Change()
  If ComboBox13.ListIndex = -1 Then Exit Sub
  ComboBox12.Value = sh.Range("AG" & ComboBox13.ListIndex + 4).Value
End Sub

Private Sub UserForm_Activate()
  Dim lr As Long
  Set sh = ActiveWorkbook.Sheets("Other_Details")
  lr = sh.Range("AG" & Rows.Count).End(xlUp).Row
  ComboBox12.List = sh.Range("AG4:AG" & lr).Value
  ComboBox13.List = sh.Range("AH4:AH" & lr).Value
End Sub

 

[Vishwanath Korgaonkar]

I done think there is issue in the code. Same issue occurs .It displays value in combobox12 when criteria is combobox13 but gives blank in combobox 13 when criteria is combobox12.The code works on other 2 interdepended comboxes in the same userform.Is it any thing to do with property of combobox or data in the range which I have to check?.
Both comboboxes are searchable and matchentry is set to 2-frmmatchentry none.
can you guide on this

ComboBox12.Value = sh.Range("AG" & ComboBox13.ListIndex + 4).Value

 

[DanteAmor]

Try this:

- You must keep this property: 2-frmmatchentry none.
- If combo12 is numeric then I am using val( ) function.
- If combo13 is text, then I am using the lcase( ) function.
- I added the global variable "searching".

Copy all the code.

  Dim sh As Worksheet   'At the beginning of all the code
  Dim searching As Boolean

Private Sub ComboBox12_Change()
  Dim i As Long
  If searching = True Then Exit Sub

  searching = True
    ComboBox13.Value = ""
    For i = 0 To ComboBox12.ListCount - 1
      If ComboBox12.List(i) = Val(ComboBox12.Value) Then
        ComboBox12.ListIndex = i
        ComboBox13.Value = sh.Range("AH" & ComboBox12.ListIndex + 4).Value
        Exit For
      End If
    Next
  searching = False
End Sub

Private Sub ComboBox13_Change()
  Dim i As Long
  If searching = True Then Exit Sub

  searching = True
    ComboBox12.Value = ""
    For i = 0 To ComboBox13.ListCount - 1
      If LCase(ComboBox13.List(i)) = LCase(ComboBox13.Value) Then
        ComboBox13.ListIndex = i
        ComboBox12.Value = sh.Range("AG" & ComboBox13.ListIndex + 4).Value
        Exit For
      End If
    Next
  searching = False
End Sub

Private Sub UserForm_Activate()
  Dim lr As Long
  Set sh = ActiveWorkbook.Sheets("Other_Details")
  lr = sh.Range("AG" & Rows.Count).End(xlUp).Row
  ComboBox12.List = sh.Range("AG4:AG" & lr).Value
  ComboBox13.List = sh.Range("AH4:AH" & lr).Value
End Sub

 

[Vishwanath Korgaonkar]

Thanks DhanteAmor,
The code works fine