Menu Title code is where?

[Guest]

I have a spreadsheet that has a menu bar item (on the same line with
File, Edit, View etc. ) that is mispelled. I can't find that menu bar code.
I can find the
macros that it points to, but where is the code for the menu bar itself?
Help?

 

[Nigel]

This is because the menu item has been added using the Excel Customize
Toolbar wizard. There effectively is no code (other than any assigned
macro). The toolbar settings are stored in an .xlb file for the user and
loaded when Excel opens.

Right click on an empty space in the toolbar area and the customize dialog
will open, then right-click the item and you should be able to edit the
name.

 

[Greg Wilson]

When you say that the menu item with the misspelled caption is specific to
the particular spreadsheet and is on the Worksheet Menu Bar, then this seems
to suggest that it is created programmatically on workbook open and deleted
on close (and/or is set to Temporary). If so, then such code is usually
called with the Workbook_Open event (ThisWorkbook module) or
Workbook_Activate event or with an Autpen macro (standard module). The
code may be contained in the above or it may be an ouside procedure that is
called.

If the above is correct, then I suggest that you simply do a search for the
misspelled caption from the VBE using the Find utility. Activate any code
module and then click the binocular icon (Find) on the VBE's Standard
toolbar. Ensure that the Current Project search option is selected.

If the menu item is can be seen on the Worksheet Menu Bar when any workbook
is open, then either of the following will work if the menu item will not be
recreated programmatically:

Manual method:
1. Right click the Worksheet Menu Bar
2. Select Customize
3. Select the Commands tab (if not already active)
4. Click the menu item you want to change
5. Click the Modify Selection button
6. Type the corrected caption into the dialog's Name field.

Programmatic method:
Paste this to a standard code module and run. Change the captions to the
appropriate text:

Sub ChangeCaption()
With Application.CommandBars(1)
.Controls("Mispelled").Caption = "Misspelled"
End With
End Sub

Regards,
Greg

 

[Guest]

this is the code I fouind that was run at startup
Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''
' Make sure the menus aren't duplicated
Call DeleteMenu
' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem =
MenuItem.Controls.add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Where is this "MenuSheet" that it refers to. That must contain the list of
reports

 

[Guest]

Nevermind, I found the hidden sheet! Thank you both.