i need help please ??

[bumajed88]

I need help please ??

I want this formula to search all sheets :


Private Sub TextBox2_Change()
ListBox1.Clear
ListBox1.RowSource = ""
For Each c In Range("d17:d200")
b = InStr(c, TextBox2.Value)
If b > 0 Then
ListBox1.AddItem c
c.Select
End If
Next c
TextBox3.Value = ActiveCell.Offset(0, 1).Value
On Error Resume Next
x = WorksheetFunction.VLookup(TextBox2.Text, Range("d1750"), 13, 0)
Image1.Picture = LoadPicture(x)
End Sub

 

[portews]

Changes in Red. Not tested. The With statment should be where the ListBox and TextBox is located. Note the periods before the LisBox and TextBox locations. I also remmed out the select. You don't need it, but you can put it back in if you want it.

Private Sub TextBox2_Change()
[COLOR=#ff0000]Dim wks as Worksheet[/COLOR]
[COLOR=#ff0000]With UserForm1[/COLOR]
     [COLOR=#ff0000].[/COLOR]ListBox1.Clear
     [COLOR=#ff0000].[/COLOR]ListBox1.RowSource = ""
     [COLOR=#ff0000]For Each wks in ThisWorkbook.Worksheets[/COLOR]
           For Each c In Range("d17:d200")
               b = InStr(c, [COLOR=#ff0000].[/COLOR]TextBox2.Value)
               If b > 0 Then
                    [COLOR=#ff0000].[/COLOR]ListBox1.AddItem c
'                    [COLOR=#00ff00]c.Select[/COLOR]
                End If
[COLOR=#333333]           Next c
[/COLOR]     [COLOR=#ff0000]Next wks[/COLOR]
[COLOR=#333333]     .TextBox3.Value = ActiveCell.Offset(0, 1).Value
[/COLOR]End With
[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]x = WorksheetFunction.VLookup(TextBox2.Text, Range("d17:p50"), 13, 0)[/COLOR]
[COLOR=#333333]Image1.Picture = LoadPicture(x)[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[bumajed88]

thank you for your response but still not searching in all sheets

please if you can help me, this is the excel file :

https://drive.google.com/file/d/0B-G641EDm9EAcFNzV0x5QVVlSVk/view

 

[portews]

I stepped thru the code below and it does go thru each worksheet and return the characters found in ListBox1. (I also added a lastrow to stop if from going all the way to 200).

I'm not sure what you want to do with TextBox3-7. It looks like it was (and is) only returning the offsets from the last value. Do you want to have values from all the matches, or just the last one?

Private Sub TextBox2_Change()
Dim wks As Worksheet
With UserForm2
    .ListBox1.Clear
    .ListBox1.RowSource = ""
  For Each wks In ThisWorkbook.Worksheets
    wks.Activate
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row
    For Each c In wks.Range("d17:d" & lastrow)
        b = InStr(c, TextBox2.Value)
        If b > 0 Then
            .ListBox1.AddItem c
            c.Select
        End If
    Next c
  Next wks
    .TextBox3.Value = ActiveCell.Offset(0, 1).Value
    .TextBox7.Value = ActiveCell.Offset(0, 2).Value
    .TextBox4.Value = ActiveCell.Offset(0, 3).Value
    .TextBox8.Value = ActiveCell.Offset(0, 4).Value
    .TextBox5.Value = ActiveCell.Offset(0, 5).Value
    .TextBox10.Value = ActiveCell.Offset(0, 6).Value
    .TextBox9.Value = ActiveCell.Offset(0, 9).Value
    .TextBox6.Value = ActiveCell.Offset(0, 10).Value
    .TextBox11.Value = ActiveCell.Offset(0, 11).Value
On Error Resume Next
End With
x = WorksheetFunction.VLookup(TextBox2.Text, Range("d17:p50"), 13, 0)
Image1.Picture = LoadPicture(x)
End Sub

 

[bumajed88]

about TextBox3-7 the remaining of search diteals like (textbox3 for the scientific qualification, textbox4 for The staffing, textbox5 for Civil Registry, ............... to textbox11 and x for image ) so after search and found the name in textbox2 give other details in boxex form textbox3 - textbox11 .


so please could you help me in that ?


and thank you very much for your helping ...

 

[bumajed88]

about TextBox3-7 the remaining of search diteals like (textbox3 for the scientific qualification, textbox4 for The staffing, textbox5 for Civil Registry, ............... to textbox11 and x for image ) so after search and found the name in textbox2 give other details in boxex form textbox3 - textbox11 .


so please could you help me in that ?

Please healppppp !?!!?!!

 

[bumajed88]

about TextBox3-7 the remaining of search diteals like (textbox3 for the scientific qualification, textbox4 for The staffing, textbox5 for Civil Registry, ............... to textbox11 and x for image ) so after search and found the name in textbox2 give other details in boxex form textbox3 - textbox11 .


so please could you help me in that ? please any help because now searching in all sheets for names only but other diteals in box 3-11 not searching in all sheets ??????????!!!!!!!