how can i search for sheet with any part of sheet name

[abu abdullah........halsaeed85]

hi every on
i need code for userform of textbox and listbox to search for any
sheet in workbook with any part of that sheet name on i enter on
textbox to populate on listbox all sheets named contain that enterd
text on textbox .
any help appreciated .
thanks

 

[John Bundy]

This code takes a string and searches the sheet name for it. Set strData to
your textbox value and instead of msgbox, do a listbox.add

Dim strData As String
strData = "Dat"
For i = 1 To ThisWorkbook.Sheets.Count
For j = 1 To Len(ThisWorkbook.Sheets(i).Name) - Len(strData) + 1
If UCase(Mid(ThisWorkbook.Sheets(i).Name, j, Len(strData))) = UCase(strData)
Then
MsgBox ThisWorkbook.Sheets(i).Name
End If
Next
Next

 

[Rick Rothstein]

Give this code a try...

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If UCase(WS.Name) Like "*" & UCase(TextBox1.Text) & "*" Then
ListBox1.AddItem WS.Name
End If
Next

Note that I used the default names for the TextBox and ListBox... change
them as required.

 

[abu abdullah........halsaeed85]

Give this code a try...

  Dim WS As Worksheet
  For Each WS In ThisWorkbook.Worksheets
    If UCase(WS.Name) Like "*" & UCase(TextBox1.Text) & "*" Then
      ListBox1.AddItem WS.Name
    End If
  Next

Note that I used the default names for the TextBox and ListBox... change
them as required.

--
Rick (MVP - Excel)






- Show quoted text -

John thanks a lot

thank you so much Rick

i used your terrific code , i add somthing i thought it gives nice
touch and now it works great here is the full code now :
Private Sub TextBox1_Change()
Dim wS As Worksheet
M = TextBox1.Text
ListBox1.Clear
If M = "" Then GoTo 1
For Each wS In ThisWorkbook.Worksheets
If UCase(wS.Name) Like "*" & UCase(TextBox1.Text) & "*" Then
ListBox1.AddItem wS.Name
End If
Next
1 End Sub

Private Sub CommandButton1_Click()
Ested3a
End Sub

Private Sub CommandButton2_Click()
End
End Sub
Private Sub UserForm_Initialize()
Dim wS As Worksheet
With ListBox1
.Clear
For Each wS In Worksheets
.AddItem wS.Name
Next
End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
CallSheet
End Sub

Private Sub CallSheet()
If ListBox1.ListIndex > -1 Then
Sheets(ListBox1.Value).Activate
End If
End Sub