البحث عن إسم طالب

[waledms]

السلام عليكم ورحمه الله وبركاته ..... أرجو عمل بحث عن إسم طالب  

Book1.xlsx

[محمد هشام.]

ممكن توضيح ما هي طريقة البحث المطلوبة 

[waledms]

عند كتابه إسم الطالب تظهر بياناته 

[lionheart]

The topic must be CLOSED as you did not respond properly to Mohamed Hicham in a good way

Generally, I will share my idea but I will not extend my reply if you have more questions

 

First create a userform with TextBox1 & ListBox1 controls

Second paste the following code on userform module

Option Explicit

Private arrData, ws As Worksheet

Private Sub UserForm_Initialize()
    Dim lr As Long
    Set ws = ThisWorkbook.Worksheets(1)
    lr = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    arrData = ws.Range("C3:C" & lr).Value
    Me.ListBox1.List = Application.Transpose(arrData)
End Sub

Private Sub TextBox1_Change()
    Dim txt As String, i As Long
    Me.ListBox1.Clear
    If Len(Me.TextBox1.Value) = 0 Then Me.ListBox1.List = Application.Transpose(arrData): Exit Sub
    txt = Me.TextBox1.Value
    For i = LBound(arrData) To UBound(arrData)
        If InStr(LCase(arrData(i, 1)), LCase(txt)) > 0 Then
            Me.ListBox1.AddItem arrData(i, 1)
        End If
    Next i
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x
    x = Application.Match(ListBox1.Value, ws.Columns(3), 0)
    With ws.Range("H3").Resize(, 4)
        .ClearContents
        If Not IsError(x) Then
            .Value = ws.Range("B" & x).Resize(, 4).Value
            Unload Me
        End If
    End With
End Sub

 

Now right-click the worksheet name and select [View Code] and paste the following code

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$H$3" Then
        Cancel = True
        UserForm1.Show
    End If
End Sub

 

The code usage

--------------------

Double click on cell H3 and the form will be shown then type some letters of the name you need to search and finally double click the name on the listbox to get the results in the range H3 to K3

Regards

 

[مصطفى زكريا]

في 7‏/6‏/2023 at 20:13, waledms said:

السلام عليكم ورحمه الله وبركاته ..... أرجو عمل بحث عن إسم طالب  

Book1.xlsx 55.25 kB · 50 downloads

 

بفضل الله تم اضافة التعديل على الملف وعمل استعلام باسم التلميذ وجلب باقى البيانات

 

Book1.mz.xlsx