بحث بواسطه textbox

[checles]

السلام عليكم 

برجاء المساعده في كتابة كود 

عند كتابة الاكود في textbox1 تظهر الوصف في textbox2 تلقائي 

وشكرا test.xlsm

[محمد هشام.]

جرب هذا

Private Sub TextBox1_change()
Dim n As Range, J As Long, f As Long
Set WS = Worksheets("Sheet1")
clé = Me.TextBox1
f = WS.Cells(WS.Rows.Count, 2).End(xlUp).Row
With WS
   Set n = .Range("A2:A" & f).Find(What:=clé, LookIn:=xlValues, LookAt:=xlWhole, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False)
    If Not n Is Nothing Then
      J = n.Row
      Me.TextBox2 = .Range("B" & J)
      Else
      Me.TextBox2 = ""
    End If
  End With 
End Sub

 

بطريقة مختلفة 

Public Property Get WS() As Worksheet: Set WS = Worksheets("Sheet1")
End Property

Private Sub UserForm_Initialize()
Set J = CreateObject("Scripting.Dictionary")
  a = WS.Range("A2:A" & WS.[A65000].End(xlUp).Row)
  For i = LBound(a) To UBound(a)
    If a(i, 1) <> "" Then J(a(i, 1)) = ""
  Next i
  n = J.keys
  Me.ComboBox1.List = n
End Sub
'==================
Private Sub ComboBox1_Change()
Me.TextBox2 = Evaluate("=vlookup(" & """" & Me.ComboBox1.Value & """" & _
  ",A2:B" & Split(WS.[a2].CurrentRegion.Address, "$")(4) & ",2)")
End Sub

 

 

 

test.xlsm

تم تعديل يوليو 31, 2024 بواسطه محمد هشام.

[checles]

شكرا لي حضرتك جزاك الله خيرا