Creating a menu of worksheets

[JonRowland]

Hi,

I looking to create a toolbar menu which will contain names of worksheets within my spreadsheet allow a quick naviagation, ie select the sheet name you want and you get taken there. The menu will ignore certain defined worksheet names.

So for example I have 4 worksheets. I want to ignore the sheet called IGNORE and the others (eg 4Menu1, 4Menu2, 4Menu3) I want to be included in a new menu called SheetM. As I add worksheets I would like to recreate the menu so it includes my new ones.

I've got so far but not sure how I can capture the sheetnames and get then as a caption within the menu and then to navigate to that sheet when selected.

This is what I've come up with so far. Can anyone help? This is aimed at XL97 as well as 2007.

Sub Sheet_Menu()
Dim SheetMenuSubItem As Object
Dim VLastusedrow As Long
Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets

CommandBars("Worksheet menu bar").Controls.Add(Type:=msoControlPopup).Caption = "SheetM"

For Each wSht In allwShts
If wSht.Name <> "IGNORE" Then wSht.Activate

End If
Next

Set SheetMenuSubItem = CommandBars("worksheet menu bar").Controls("SheetM")
With SheetMenuSubItem
.Controls.Add(Type:=msoControlButton).Caption = "SHEET NAME HERE"
.Controls("Add New DataSheet").OnAction = "TAKE ME TO THE SHEET"
End With

End Sub

Thanks
Jon

 

[ravishankar]

Hi
The following code lists sheet names in col A of current sheet and provides link. Just click on the sheet of your choice

Sub links()
Dim a As Long
For a = 1 To Sheets.Count
    If Worksheets(a).Name <> "IGNORE" And Worksheets(a).Name <> ActiveSheet.Name Then
        Range("A" & a + 1).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        Worksheets(a).Name & "!A1", TextToDisplay:=Worksheets(a).Name
    End If
Next a
MsgBox "Complete"
End Sub

Ravi

 

[JonRowland]

hI ravishankar,

Not what I want as I need the sheet selection created as menubar. Using some of your code, which has helped, I can now get the menubar create with all worksheets in the workbook. Problem I have is that a) Includes IGNORE in the menu and b) cannot naviagate to the sheet selected from the menu option. Anyone able to help?

Jon


Sub Sheet_Menu()

Dim SheetMenuSubItem As Object
Dim a As Long

CommandBars("Worksheet menu bar").Controls.Add(Type:=msoControlPopup).Caption = "SheetM"

For a = 1 To Sheets.Count
If Worksheets(a).Name <> "IGNORE" Then ' And Worksheets(a).Name <> ActiveSheet.Name Then
Range("A" & a + 1).Select

Set SheetMenuSubItem = CommandBars("worksheet menu bar").Controls("SheetM")
With SheetMenuSubItem
.Controls.Add(Type:=msoControlButton).Caption = Worksheets(a).Name
.Controls(Worksheets(a).Name).OnAction = "Sheets(Worksheets (a)).Select"

End With
End If
Next a

End Sub

 

[JonRowland]

Right found this code at http://socko.wordpress.com/2008/05/31/sheetnav/
Option Explicit
Dim i As Integer

Sub Add_Cmb_in_Commandbar()
On Error Resume Next
Dim myBar As CommandBar
Dim myControl As CommandBarControl

Set myBar = Application.CommandBars("standard")
Set myControl = myBar.Controls.Add(Type:=msoControlComboBox, ID:=1)

With myControl
For i = 1 To ThisWorkbook.Sheets.Count
.AddItem Text:=ThisWorkbook.Sheets(i).Name, Index:=i
Next igo
.Visible = True
.Caption = "selva"
.DropDownLines = 4
.DropDownWidth = 100
.ListHeaderCount = 0
.OnAction = "ProcessSelection"
End With
End Sub

Does mostly what I want with the exception of ignoring the sheet I don't want in the menu. Everything I tried doesn't ignore so please please can anyone help?
Jon

 

[VoG]

Perhaps

Sub Add_Cmb_in_Commandbar()
Dim myBar As CommandBar
Dim myControl As CommandBarControl
Dim i As Integer
Set myBar = Application.CommandBars("standard")
Set myControl = myBar.Controls.Add(Type:=msoControlComboBox, ID:=1)
With myControl
    For i = 1 To ThisWorkbook.Sheets.Count
        If ws(i).Name <> "IGNORE" Then .AddItem Text:=ThisWorkbook.Sheets(i).Name, Index:=i
    Next i
    .Visible = True
    .Caption = "selva"
    .DropDownLines = 4
    .DropDownWidth = 100
    .ListHeaderCount = 0
    .OnAction = "ProcessSelection"
End With
End Sub

 

[Tom Urtis]

This will place a combobox on the menu bar of your workbook in version 2003, or on the Ribbon in the Addin tab that will be created by this code for version 2007.

Step 1
Find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedures into the large white area that is the workbook module, or for version 2007, press Alt+F11 and right click on the ThisWorkbook object module in the VBAProject pane for your workbook:

Private Sub Workbook_Open()
Run "ResetMenu"
Run "MakeCBO"
Dim TymeOfDay$
If Time < 0.5 Then
TymeOfDay = "Good Morning !!" & vbCrLf & vbCrLf
ElseIf Time >= 0.5 And Time < 0.75 Then
TymeOfDay = "Good Afternoon !!" & vbCrLf & vbCrLf
Else
TymeOfDay = "Good Evening !!" & vbCrLf & vbCrLf
End If
MsgBox _
TymeOfDay & _
"To quickly and easily activate any sheet," & vbCrLf & _
"select a sheet name from the drop-down list" & vbCrLf & _
"that is located on the menu bar near the top" & vbCrLf & _
"of the screen, just to the left of ''Help''.", _
64, "Sheet navigation tip:"
End Sub
 
Private Sub Workbook_Activate()
Run "ResetMenu"
Run "MakeCBO"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "ResetMenu"
ThisWorkbook.Save
End Sub
 
Private Sub Workbook_Deactivate()
Run "ResetMenu"
End Sub

Step 2
While you are still in the Visual Basic Editor, from that menu bar, click Insert > Module and paste the following code into that new module:

Option Compare Text
 
Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
Err.Clear
End Sub
 
Private Sub MakeCBO()
With Application
.ScreenUpdating = False
Run "ResetMenu"
Dim cboSheetz As CommandBarComboBox, ws As Worksheet
With .CommandBars("Worksheet Menu Bar")
Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, before:=.Controls.Count)
End With
With cboSheetz
.Caption = "Sheet selector"
.OnAction = "mySheet"
End With
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
If ws.name <> "IGNORE" and ws.Name <> "4Menu1" and ws.name <> "4Menu2" and ws.Name <> "4Menu3" Then
cboSheetz.AddItem ws.Name
End If
End if
Next ws
cboSheetz.ListIndex = 1
.ScreenUpdating = True
End With
End Sub
 
Private Sub mySheet()
With CommandBars("Worksheet Menu Bar").Controls("Sheet selector")
Worksheets(.List(.ListIndex)).Activate
End With
End Sub

Step 3
Press Alt+Q to return to the worksheet.

Step 4
Save the workbook.

Step 5
Close the workbook.

Step 6
Re-open the workbook and see if what happens is what you want, which will be:
- a message box advises the user of the combobox and what it is for.
- a combobox with visible sheets' names is on the menu bar excluding those other names too
- the combobox willl only be visible when that workbook is active.
- selecting a sheet name will take you to that sheet.
- the sheet name list will always be updated as you add new sheets, then deactivate (or close) and re-activate (or open) the workbook.

 

[JonRowland]

Tom you are a star. Absoultely what I wanted

Thanks a lot
Jon