Searching Sheets

  • Thread starter Thread starter dixonpeter
  • Start date Start date
D

dixonpeter

I need the code for this:

On my spreadsheet, I have millions of sheets, I need a function tha
can look these up by name, either by a drop down box, or by typing i
the spreadsheet name, when you press the commandbutton, it then take
you to that sheet.

Any help please
 
dixonpeter,

I hope that I understand your question correctly. Try this:

1. Right-click the little arrow symbols to the left of the sheet tabs
2. Select a sheet from the menu or click "More Sheets..."
 
Thanks for the help, unfortunatly it wasn't quite what I was lookin
for, what I'm after is a userform, which I have got to come u
automatically, that you enter the sheet name, and then press a butto
and it takes you to that sheet.

Thanks anyway :
 
I hope this is more like what you want then...

1. Start Excel with a new workbook
2. Alt+F11 (takes you to Visual Basic Editor)
3. Insert - UserForm
4. Draw a listbox and a button on the form
5. Change properties
UserForm1.Caption = "Go to sheet"
CommandButton1.Caption = "OK"
6. Double-click on the form
7. Enter this code:

Private Sub UserForm_Initialize()
Dim sh
For Each sh In ActiveWorkbook.Sheets
ListBox1.AddItem sh.Name
Next sh
ListBox1.ListIndex = 0
End Sub

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

Private Sub CommandButton1_Click()
Sheets(ListBox1.Value).Activate
Unload Me
End Sub

8. Insert - Module
9. Enter this code

Sub ShowForm()
UserForm1.Show
End Sub

10 Switch back to Excel (Alt+F11)
11. Run the VBA procedure ShowForm by pressing Alt+F8

You can also associate this macro with a shortcut key (Tools - Macro -
Macros... Options...)
 
Thanks, thats a lot more helpful, I can see how it is suppost to work
but theres still a glitch, this is the code I have:

Private Sub UserForm_Initialize()
Dim sh
For Each sh In ActiveWorkbook.Sheets
ListBox1.AddItem sh.Name
Next sh
ListBox1.ListIndex = 0
End Sub

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

Private Sub CommandButton1_Click()
Sheets(ListBox1.Value).Activate
Unload Me
End Sub

But it wont actually select the sheet, and just goes back into VB, t
be honest, I might be able to sort this out though.

Thanks for the help, its got me on the right path
 
Actually, it does work, I think I must have made a mistake somewhere
thanks a lot, incidently, do you know how to sort the sheet
automatically
 
Back
Top