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

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

--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


"abu (E-Mail Removed)" wrote:

> 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
> .
>

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)



<(E-Mail Removed)> wrote in message
news:4aaa4956-24c5-469d-8e84-(E-Mail Removed)...
> 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

On Mar 23, 6:28*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> 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)
>
> <halsaee...@gmail.com> wrote in message
>
> news:4aaa4956-24c5-469d-8e84-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - 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