Prev: Type mismatch: array or user-defined type expected
Next: how do I find all the rows containing a specific value in excel
From: abu abdullah........halsaeed85 on 23 Mar 2010 08:42
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
From: Rick Rothstein on 23 Mar 2010 11:28
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)



<halsaeed85(a)gmail.com> wrote in message
news:4aaa4956-24c5-469d-8e84-5bb2e48fc316(a)i25g2000yqm.googlegroups.com...
> 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

From: abu abdullah........halsaeed85 on 24 Mar 2010 04:02
On Mar 23, 6:28 pm, "Rick Rothstein"
<rick.newsNO.S...(a)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...(a)gmail.com> wrote in message
>
> news:4aaa4956-24c5-469d-8e84-5bb2e48fc316(a)i25g2000yqm.googlegroups.com...
>
>
>
> > 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

 | 
Pages: 1
Prev: Type mismatch: array or user-defined type expected
Next: how do I find all the rows containing a specific value in excel