Snasui Excel Forum

Code: Select all

Private Sub ComboBox3_AfterUpdate() '***
On Error Resume Next
Dim idall As Range
Dim lMatch As Long
Dim lMatch1 As Long
Dim lMatch2 As Long
Dim lCountif As Long
With Sheets("A")
Set idall = .Range("D:D")
idall.NumberFormat = "@"
lCountif = Application.CountIf(idall, ComboBox3.Text)
If ComboBox3.Text = "" Then
End If
If lCountif > 0 Then
lMatch1 = Application.Match(ComboBox3.Text, idall, 0)
lMatch2 = Application.Match(CDbl(ComboBox3.Text), idall, 0)
lMatch = lMatch1 + lMatch2
TextBox20.Text = .Range("D" & lMatch).Offset(0, -2)
TextBox21.Text = .Range("B" & lMatch).Offset(0, 6)
TextBox22.Text = .Range("D" & lMatch).Offset(0, 6)
TextBox23.Text = .Range("D" & lMatch).Offset(0, 7)
TextBox24.Text = .Range("D" & lMatch).Offset(0, 8)
TextBox26.Text = .Range("D" & lMatch).Offset(0, 13)
TextBox27.Text = .Range("D" & lMatch).Offset(0, 14)
TextBox28.Text = .Range("D" & lMatch).Offset(0, 15)
'TextBox29.Text = .Range("D" & lMatch).Offset(0, 8) 'Hide
TextBox25.Text = .Range("D" & lMatch).Offset(0, 16)
TextBox30.Text = .Range("D" & lMatch).Offset(0, 17)
ComboBox2.Text = .Range("D" & lMatch).Offset(0, 18)
ComboBox1.Text = .Range("D" & lMatch).Offset(0, 10)
TextBox1.Text = Sheets("B").Range("B" & lMatch).Offset(0, 2).Value
TextBox2.Text = Sheets("B").Range("B" & lMatch).Offset(0, 3).Value
TextBox3.Text = Sheets("B").Range("B" & lMatch).Offset(0, 4).Value
TextBox5.Text = Sheets("B").Range("B" & lMatch).Offset(0, 5).Value
TextBox4.Text = Sheets("B").Range("B" & lMatch).Offset(0, 6).Value
TextBox6.Text = Sheets("B").Range("B" & lMatch).Offset(0, 7).Value
TextBox7.Text = Sheets("B").Range("B" & lMatch).Offset(0, 8).Value
TextBox8.Text = Sheets("B").Range("B" & lMatch).Offset(0, 9).Value
TextBox9.Text = Sheets("B").Range("B" & lMatch).Offset(0, 10).Value
TextBox10.Text = Sheets("B").Range("B" & lMatch).Offset(0, 11).Value
TextBox11.Text = Sheets("B").Range("B" & lMatch).Offset(0, 12).Value
TextBox14.Text = Sheets("C").Range("B" & lMatch).Offset(0, 2).Value
TextBox13.Text = Sheets("C").Range("B" & lMatch).Offset(0, 3).Value
TextBox12.Text = Sheets("C").Range("B" & lMatch).Offset(0, 4).Value
TextBox17.Text = Sheets("C").Range("B" & lMatch).Offset(0, 5).Value
TextBox16.Text = Sheets("C").Range("B" & lMatch).Offset(0, 6).Value
TextBox15.Text = Sheets("C").Range("B" & lMatch).Offset(0, 7).Value

ComboBox3.SetFocus
'CommandButton4.TakeFocusOnClick 'error

End If
End With
End Sub