Going to sheets via keyboard shortcuts

[terry]

Is there a way to go directly to a certain sheet in a
workbook by way of a keyboard shortcut? I use <cntr>
PageUp and <cntr> PageDown all the time, but that requires
that you go sequentially from sheet to sheet until you get
where you want to go. What I'm looking for is the
equivalent of <alt>Window and then choosing the file from
the list of open files.

(Of course, there's always the mouse, but I HATE using the
mouse!)

Thanks!

[Chip Pearson]

Terry,

There isn't a built-in way to do this. However, you could write a VBA procedure
to display the sheet list, and then use the arrow keys to select the desired
sheet.

Sub SheetList()
Application.CommandBars("Workbook tabs").ShowPopup
End Sub

Assign a shortcut key to this procedure by going to the Tools menu, Macros,
Macros, selecting this procedure from the list, and choosing Options.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"terry" <tbma...@oxhp.com> wrote in message
news:f06f01c27e9a$e0322040$37ef2ecf@TKMSFTNGXA13...

[terry]

Thanks. Is there a way to make the pop-up respond to
typed letters in the same way Windows Explorer will jump
to the file as you start to type its name?

>.
>

[David McRitchie]

There must be way but don't know how.
If you could get the "more sheets" dialog to come up
immediately without having to first hit "more sheets"..

"terry" <tbma...@oxhp.com> wrote in message news:416e01c27eb5$8f7c8d00$35ef2ecf@TKMSFTNGXA11...

[Chip Pearson]

On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"David McRitchie" <dmcri...@msn.com> wrote in message
news:#Ua$4nrfCHA.1816@tkmsftngp12...

[Dave Peterson]

How about using a combination of Chip's answers:

Option Explicit
Sub testme()

On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute

If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
On Error GoTo 0

End Sub

---
Or if you want a userform, I made one with just a giant listbox in the center.
(I'd use the X to close the window.)

Option Explicit
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveWorkbook.Worksheets(CStr(Me.ListBox1.Value)).Activate
'unload me 'if you want
End Sub

Private Sub UserForm_Initialize()

Dim i As Long

Me.Caption = "Double click to activate--X to exit"
With ActiveWorkbook
For i = 1 To .Worksheets.Count
If .Worksheets(i).Visible Then
Me.ListBox1.AddItem .Worksheets(i).Name
End If
Next i
End With

End Sub

terry wrote:
>
> This gets me close. The macro does nothing unless the
> workbook has enough sheets to trigger the "more sheets.."
> box. I considered having a test on the number of sheets,
> using the popupbox if <n and the "more sheets" line if
> >=n. But then I thought of creating a User Defined Form
> with a list box contining sheet names. So far I have:
>
> Sub sheetlist2()
> Dim NumberOfSheetsInWorkbook As Integer
> NumberOfSheetsInWorkbook = Sheets.Count
> Dim TabsInWorkbook() As String
> ReDim TabsInWorkbook(1)
> For i = 1 To Sheets.Count
> ReDim Preserve TabsInWorkbook(UBound(TabsInWorkbook) +
> 1)
> TabsInWorkbook(i - 1) = Sheets(i).Name
> Next i
> ReDim Preserve TabsInWorkbook(UBound(TabsInWorkbook) - 2)
>
> My problem is now that the ListBox RowSource Property
> won't take my String Array variable. Any ideas?

> >.
> >

--

Dave Peterson
ec3...@msn.com

[terry]

This gets me close. The macro does nothing unless the
workbook has enough sheets to trigger the "more sheets.."
box. I considered having a test on the number of sheets,
using the popupbox if <n and the "more sheets" line if
>=n. But then I thought of creating a User Defined Form
with a list box contining sheet names. So far I have:

Sub sheetlist2()
Dim NumberOfSheetsInWorkbook As Integer
NumberOfSheetsInWorkbook = Sheets.Count
Dim TabsInWorkbook() As String
ReDim TabsInWorkbook(1)
For i = 1 To Sheets.Count
ReDim Preserve TabsInWorkbook(UBound(TabsInWorkbook) +
1)
TabsInWorkbook(i - 1) = Sheets(i).Name
Next i
ReDim Preserve TabsInWorkbook(UBound(TabsInWorkbook) - 2)

My problem is now that the ListBox RowSource Property
won't take my String Array variable. Any ideas?

>.
>

[terry]

I used your UserForm solution and it worked. But the whole
reason for doing it was to totally avoid using the mouse.
So I added a button with an accelerator key to the form
with code =the guts of the DoubleClick event procedure.
Voila! Works GREAT. Thanks, Dave and Chip, for sticking
with this one!

>.
>