Shortcut to find sheets

F

FC

I have a lot of worksheets by names and as I have to go back and forth never
ocurred to me that may be a way to use a shortcut to find the one I need
instead of scrolling all over. Is there a way ?
Also up to how many sheets can I have in one workbook.
Excel 2003. TX.
 
F

FSt1

hi,
one way. right click the sheet navigator at the lower left of the xl window.
this will produce a pop up listing all the sheet names.
in xl help type the word specifications.
number of sheets in a workbook - limited by available memory.

Regards
FSt1
 
J

JLatham

There are only two shortcuts for sheet navigating:
Excel Help: Keyboard Shortcuts
Move to next sheet: [Ctrl]+[Page Down]
Move to previous sheet: [Ctrl]+ [Page Up]

Other than that, using the method Fst1 gave you is the easiest, non-coding
way to go. That can end up being a 2 or 3 step process once the number of
sheets becomes larger than can be held in the basic popup window you get when
you right-click the navigator (you get option to list them all in yet another
window).

You could code up a userform with a combobox that would list all of the
sheets in the book and add a button to the menu bar to call a routine to open
that userform on demand. Then you'd select the desired sheet from the
combobox's list.

Code for the pieces would look something like this (from one I tossed
together using default names)

This goes into a regular module and would be the macro you assigned to the
added button in a toolbar:
Sub ShowQuickNav()
UserForm1.Show
End Sub

Here's code for the two controls on the userform (1 combo box, 1 command
button to close without changing sheets)

Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
ComboBox1.AddItem WS.Name
Next
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Text = "" Then
Exit Sub
End If
Worksheets(ComboBox1.Text).Activate
Unload Me
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub
 
F

FC

Thank you, keep the good work, appreciate it.

FSt1 said:
hi,
one way. right click the sheet navigator at the lower left of the xl window.
this will produce a pop up listing all the sheet names.
in xl help type the word specifications.
number of sheets in a workbook - limited by available memory.

Regards
FSt1
Click to expand...
 
F

FC

Thanks for the macro , I will be using it when I get close to 100
sheets,maybe in a couple months. Thanks for all the important information too.
Thanks a lot for your help. Take care.

JLatham said:
There are only two shortcuts for sheet navigating:
Excel Help: Keyboard Shortcuts
Move to next sheet: [Ctrl]+[Page Down]
Move to previous sheet: [Ctrl]+ [Page Up]

Other than that, using the method Fst1 gave you is the easiest, non-coding
way to go. That can end up being a 2 or 3 step process once the number of
sheets becomes larger than can be held in the basic popup window you get when
you right-click the navigator (you get option to list them all in yet another
window).

You could code up a userform with a combobox that would list all of the
sheets in the book and add a button to the menu bar to call a routine to open
that userform on demand. Then you'd select the desired sheet from the
combobox's list.

Code for the pieces would look something like this (from one I tossed
together using default names)

This goes into a regular module and would be the macro you assigned to the
added button in a toolbar:
Sub ShowQuickNav()
UserForm1.Show
End Sub

Here's code for the two controls on the userform (1 combo box, 1 command
button to close without changing sheets)

Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
ComboBox1.AddItem WS.Name
Next
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Text = "" Then
Exit Sub
End If
Worksheets(ComboBox1.Text).Activate
Unload Me
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub




FC said:
I have a lot of worksheets by names and as I have to go back and forth never
ocurred to me that may be a way to use a shortcut to find the one I need
instead of scrolling all over. Is there a way ?
Also up to how many sheets can I have in one workbook.
Excel 2003. TX.
Click to expand...
Click to expand...
 
O

opieandy

Hi,

The following code was very helpful. Is there any way to exclude certain
Worksheets from being in the Combo Box?


Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
ComboBox1.AddItem WS.Name
Next
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Text = "" Then
Exit Sub
End If
Worksheets(ComboBox1.Text).Activate
Unload Me
End Sub
 
D

Daniel.C

Hi.
Try :

Private Sub UserForm_Initialize()
Dim WS As Worksheet, Exclusion
Exclusion = Array("Sheet1", "Sheet2")
For Each WS In Worksheets
If Not IsNumeric(Application.Match(WS.Name, Exclusion, 0)) Then
ComboBox1.AddItem WS.Name
End If
Next
End Sub

Regards.
Daniel
 
Top