View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
Posts: 5,934
Default List all sheets in the work book and select by double click

Damn! All I posted were the instructions... I forgot to include the code in
my posting. Here is that code...

' *************** START OF USERFORM CODE ***************
Private Sub UserForm_Initialize()
Dim Obj As Object
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
For Each Obj In Sheets
If Obj.Visible Then 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
ListBox1.Clear
For X = 1 To Sheets.Count
If InStr(1, Sheets(X).Name, TextBox1.Text, vbTextCompare) = 1 And _
Sheets(X).Visible Then ListBox1.AddItem Sheets(X).Name
Next
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
' *************** END OF CODE ***************