Forum Home
Press F1
 
Thread ID: 60054 2005-07-21 06:42:00 Excel Menu? bk T (215) Press F1
Post ID Timestamp Content User
374064 2005-07-21 06:42:00 This Excel workbook contains 20 worksheets of different contents. Is it possible to create a "Menu" where I can select the disired worksheet to work on? Of course, I can click the worksheet tabs at the bottom but I think it would be much more 'presentable' if there is a 'menu' or an index.

Cheers 		bk T (215)
374065 2005-07-21 17:19:00 There are several possibilities:-

You can have a 'Menu' sheet that has a combo box (drop down list) on it of all your sheets, you can have a menu in the menu bar (ie where File,Edit,View etc are located) or a drop-down list in a standard toolbar.

Menu Sheet - No Code
Using a combobox can be done without code but means you have to manually update a list when you delete/add sheets. It also means that the list is only available in the workbook where its created.

Code
Using code allows the list to be dynamic so it automatically updates when sheets are added/deleted and can be alphabetically sorted to make it easier to find sheets. The code could be created as an Add-In meaning that the option will be available for all workbooks, even new ones. With code it means that unless you know how to write it your stuck. However, I cant see much needed for changes once it is created.

Let me know what option you would like and I can assist you.

regards
Graham. 		Parry (5696)
374066 2005-07-21 18:51:00 You could try using Excels Hyperlink function under the Insert menu.

This allows you to assign a description to a link - you then click the link (as you would whilst on the net) and you are taken to the address specified.

You may need to read the help item on it though 		TeejayR (4271)
374067 2005-07-21 19:16:00 If you want to create these manually the formula is

=HYPERLINK("[WorkbookName]SheetName!CellAddress","NameToDisplay")

eg the following formula named Sheet4 takes you to cell a1 in Sheet4 in the same worksheet.
HYPERLINK("[testlinks.xls]Sheet4!A1","Sheet4")

If you need to access another worksheet you need to give the complete path. 		TeejayR (4271)
374068 2005-07-22 07:37:00 There are several possibilities:-

You can have a 'Menu' sheet that has a combo box (drop down list) on it of all your sheets, you can have a menu in the menu bar (ie where File,Edit,View etc are located) or a drop-down list in a standard toolbar.

Menu Sheet - No Code
Using a combobox can be done without code but means you have to manually update a list when you delete/add sheets. It also means that the list is only available in the workbook where its created.

Code
Using code allows the list to be dynamic so it automatically updates when sheets are added/deleted and can be alphabetically sorted to make it easier to find sheets. The code could be created as an Add-In meaning that the option will be available for all workbooks, even new ones. With code it means that unless you know how to write it your stuck. However, I cant see much needed for changes once it is created.

Let me know what option you would like and I can assist you.

regards
Graham.

Hi Parry, the with Code option sounds great. How easy (or difficult) it is to create one?

If it is too difficult then I may opt for the without Code option (should be simpler?).

Thanks. 		bk T (215)
374069 2005-07-22 23:00:00 Hi Parry, the with Code option sounds great. How easy (or difficult) it is to create one?

If it is too difficult then I may opt for the without Code option (should be simpler?).

Thanks.

Parry, would really appreciate it if you could help. Thanks 		bk T (215)
374070 2005-07-23 00:07:00 Try this...

1. Open Excel in a new book - make sure all other books are closed.
2. Select Tools|Macro|Visual Basic Editor (or ALT+F11)
3. Select Insert|Class Module from the VBE menu
4. Paste the following code in the right hand window

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
Application.Run " GoToSheets.xla!CreateMenu "
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Application.Run " GoToSheets.xla!CreateMenu "
End Sub
5. Select Insert|Module from the VBE menu
6. Paste the following code in the right hand window

Option Explicit
Public x As New Class1

Sub InitializeApp()
Set x.App = Application
End Sub

Sub Auto_Open()
On Error GoTo ErrHandler
Application.Run " GoToSheets.xla!InitializeApp "
Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'Auto_Open' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub Auto_Close()
On Error GoTo ErrHandler
Application.Run " GoToSheets.xla!DeleteMenu "
Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'Auto_Close' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub App_SheetActivate()
On Error GoTo ErrHandler
Application.Run " GoToSheets.xla!CreateMenu "
Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'App_SheetActivate' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub App_SheetDeactivate()
On Error GoTo ErrHandler
Application.Run " GoToSheets.xla!CreateMenu "
Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'App_SheetDeactivate' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub App_WorkbookDeactivate()
On Error GoTo ErrHandler
Application.Run " GoToSheets.xla!CreateMenu "
Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'App_WorkbookDeactivate' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub CreateMenu()
Dim MenuObject, MenuItem As Object, n As Long, i As Long
Dim SubMenuItem, Sh As Variant, TmpArr() As String

On Error GoTo ErrHandler

' Make sure the menus aren't duplicated
Application.Run " GoToSheets.xla!DeleteMenu "

'Add menu item
Set MenuItem = Application.CommandBars( " Formatting " ).Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = " & GoTo Sheet "

'Add Rebuild item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = " <<Refresh Sheet List>> "
SubMenuItem.OnAction = " GoToSheets.xla!createmenu "
SubMenuItem.FaceId = 480

'Create a workbook variable
Dim Wb As Workbook
On Error Resume Next
Set Wb = ActiveWorkbook
If Wb Is Nothing Or Err.Number <> 0 Or Wb.Name = ThisWorkbook.Name Then
'Pause because new workbook display is too slow
For i = 1 To 2
Application.Wait (Now + TimeValue( " 0:00:01 " ))
Set Wb = ActiveWorkbook
If Not Wb Is Nothing Then Err.Clear: Exit For
Next i
'If still an error ask user to rebuild menu manually
If Wb Is Nothing Or Err.Number <> 0 Then
MsgBox " The 'GoTo Sheet' menu list could not be built properly. " & vbLf & _
" Ensure a workbook is open then click on 'Refresh Sheet List' " & vbLf & _
" option in the 'GoTo Sheet' menu to try again. " , _
vbExclamation, " Error When Building Goto Sheet Menu "
Exit Sub
End If
End If
On Error GoTo ErrHandler

'Build sheet names
For Each Sh In Wb.Sheets
If Sh.Visible Then
On Error Resume Next
ReDim Preserve TmpArr(1 To UBound(TmpArr) + 1)
If Err.Number <> 0 Then ReDim TmpArr(1 To 1)
On Error GoTo 0
TmpArr(UBound(TmpArr)) = Sh.Name
End If
Next Sh

'Alpha sort array
BubbleSort TmpArr

'Add sheets to menu
For n = 1 To UBound(TmpArr)
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = TmpArr(n)
SubMenuItem.OnAction = " 'LinkSheet( " & Wb.Sheets(TmpArr(n)).Index & " )' "
If Wb.ActiveSheet.Name = TmpArr(n) Then
SubMenuItem.FaceId = 1087
Else
SubMenuItem.FaceId = 0
End If
Next n

Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'CreateMenu' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub LinkSheet(ShtName As Integer)
On Error GoTo ErrHandler
If IsMissing(ShtName) Then Exit Sub

On Error Resume Next
ActiveWorkbook.Sheets(ShtName).Activate
Application.Run " GoToSheets.xla!CreateMenu "
Err.Clear
On Error GoTo ErrHandler

Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'LinkSheet' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source
End Sub

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus
On Error Resume Next
Application.CommandBars( " Formatting " ).Controls( " & GoTo Sheet " ).Delete
On Error GoTo 0
End Sub

Sub BubbleSort(List() As String)
'Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As String

On Error GoTo ErrHandler

First = LBound(List)
Last = UBound(List)


For i = First To Last - 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i

Exit Sub
ErrHandler:
MsgBox " The following error has occurred with the 'BubbleSort' procedure:- " & vbLf & vbLf & _
" Error Number: " & Err.Number & vbLf & _
" Error Description: " & Err.Description, vbCritical, _
" Error in project " & Err.Source

End Sub
7. Select File|Close and return to Microsoft Excel (or ALT+Q)
8. Select FIle|SaveAs from the menu and change the SaveAs Type drop down box to Microsoft Office Excel Addin (bottom of list) and name the file GoToSheets.xla
9. Close Excel and re-open it again
10. Choose Tools|Addins from the menu then select the option GoToSheets so it has a tick against it

Notes
* You should now see a menu item GoTo Sheets in the formatting toolbar. IF not close and re-open Excel
* The active sheet will have a tick against it in this menu
* The Refresh List Option may be required if there was trouble building the sheet menu or you changed a sheet name.
* Certain events will automatically rebuild the menu such as changing to a new workbook (so the menu reflects the new book) or changing sheets.

Any probs let me know.

cheers
Graham 		Parry (5696)
374071 2005-07-23 02:54:00 Last step will require you to click the browse button under Tools|Addins to locate the addin file. Parry (5696)
374072 2005-07-23 12:32:00 Hi Parry

Tried your codes but could not get Excel to show the menu item GoToSheets.

Re-opened and re-started windows a few times but GoToSheet item still not available. Checked the Tools |Addin and found that the GoToSheet is ticked.

Where is the "Refresh List Option"?

Please advise. 		bk T (215)
374073 2005-07-23 16:57:00 It should appear in the formatting toolbar. On the far right of the toolbar is an arrow that if you click displays Add or Remove buttons so click on this to get the list of items - perhaps you arent displaying all the options. The refresh option appears in the list menu. Did you name the file correctly as GoToSheets.xla? Note there are no spaces in the name. Parry (5696)
1 2