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.

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

"FC" wrote:

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.

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" wrote:

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.

Thank you, keep the good work, appreciate it.

"FSt1" wrote:

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

"FC" wrote:

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.

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" wrote:

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" wrote:

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.

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

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

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