PDA

View Full Version : Solved: Find text in a Userform Listbox



Brian Curtis
12-17-2012, 01:28 PM
:giggle I have a workbook with 400+ worksheets.
To go to a specific sheet I have a Userform with a single listbox.
Is it possible to add a text box that could be used with a Find command to locate the sheets

snb
12-17-2012, 02:07 PM
if you populate a combobox with all the sheetnames you can use the combobox for that purpose.


private Sub Userform_Initialize()
for each sh in sheets
c01=c01 & vbLf & sh.name
next
Combobox1.list=split(mid(c01,2),vblf)
End Sub

Daxton A.
12-18-2012, 07:30 PM
"I agree with SNB, but here is the other way as well:"

Private Sub cmdSearch_Click()
Dim i As Integer

For i = 0 To (lstPool.ListCount - 1)

If LCase(frmCurrentSheets.lstPool.List(i)) = LCase(txtSearch.Text) Then
lstPool.Selected(i) = True
End If

Next

End Sub

Brian Curtis
12-22-2012, 09:21 AM
:giggle I have a workbook with 400+ worksheets.
To go to a specific sheet I have a Userform with a single listbox.
Is it possible to add a text box that could be used with a Find command to locate the sheets
Solved: I found an article by Rick Rothstein (selecting-worksheets-combobox-dropdown-value) That was exactly what I was looking for. Thanks for your help.

Aussiebear
12-22-2012, 05:08 PM
AS in
http://www.mrexcel.com/forum/excel-questions/642005-selecting-worksheets-combobox-dropdown-value.html and scroll down to post #5

Trebor76
12-22-2012, 08:11 PM
You can simply right-click on the navigation buttons at the botton left-hand corner of any workbook and then select (click on) the desired worksheet.

Brian Curtis
12-24-2012, 06:04 AM
Dim SheetNames() As String
Private Sub UserForm_Initialize()
Dim Obj As Object
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
ReDim SheetNames(0 To Sheets.Count - 1)
For Each Obj In Sheets
SheetNames(Obj.Index - 1) = Obj.Name
ListBox1.AddItem Obj.Name
Next
TextBox1.SetFocus
End Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With TextBox1
If KeyCode = vbKeyLeft Then
ListBox1.ListIndex = -1
.SelStart = Len(.Text)
.SetFocus
ElseIf KeyCode = vbKeyReturn Then
If ListBox1.ListCount > 0 Then
Sheets(ListBox1.Text).Activate
Unload Me
End If
End If
End With
End Sub
Dim SheetNames() As String
Private Sub UserForm_Initialize()
Dim Obj As Object
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
ReDim SheetNames(0 To Sheets.Count - 1)
For Each Obj In Sheets
SheetNames(Obj.Index - 1) = Obj.Name
ListBox1.AddItem Obj.Name
Next
TextBox1.SetFocus
End Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With TextBox1
If KeyCode = vbKeyLeft Then
ListBox1.ListIndex = -1
.SelStart = Len(.Text)
.SetFocus
ElseIf KeyCode = vbKeyReturn Then
If ListBox1.ListCount > 0 Then
Sheets(ListBox1.Text).Activate
Unload Me
End If
End If
End With
End Sub
Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Sheets(ListBox1.List(ListBox1.ListIndex)).Activate
Unload Me
End Sub
Private Sub TextBox1_Change()
Dim X As Long
Dim Pages() As String
Pages = Filter(SheetNames, TextBox1.Text, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UBound(Pages) > -1 Then
With ListBox1
.Clear
For X = 0 To UBound(Pages)
.AddItem Mid$(Pages(X), 1)
Next
End With
Else
ListBox1.Clear
End If
Else
ListBox1.Clear
For X = 0 To UBound(SheetNames)
ListBox1.AddItem Mid$(SheetNames(X), 2)
Next
End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With ListBox1
If KeyCode = vbKeyReturn Then
KeyCode = 0
If .ListCount = 0 Then
Exit Sub
ElseIf .ListCount = 1 Then
Sheets(.List(0)).Activate
Unload Me
Else
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
ElseIf (KeyCode = vbKeyDown Or (KeyCode = vbKeyRight And _
TextBox1.SelStart = Len(TextBox1.Text))) And .ListCount > 0 Then
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
End With
End Sub

:giggle I have a workbook with 400+ worksheets.
To go to a specific sheet I have a Userform with a single listbox.
Is it possible to add a text box that could be used with a Find command to locate the sheets