(Of course, there's always the mouse, but I HATE using the
mouse!)
Thanks!
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" <tbma...@oxhp.com> wrote in message news:416e01c27eb5$8f7c8d00$35ef2ecf@TKMSFTNGXA11...
--
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...
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
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?
>.
>
>.
>