Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Title code is where?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Title code is where?
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. -- Cheers Nigel wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Title code is where?
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 Auto_Open 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 " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Title code is where?
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, _ Befo=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 "Greg Wilson" wrote in message ... 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 Auto_Open 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 " wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Title code is where?
Nevermind, I found the hidden sheet! Thank you both.
"Greg Wilson" wrote in message ... 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 Auto_Open 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 " wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Menu Title | Excel Worksheet Functions | |||
[1] in the title menu | Excel Discussion (Misc queries) | |||
[1] in the title menu | Excel Discussion (Misc queries) | |||
Title in dropdown menu to stay | Excel Discussion (Misc queries) | |||
VBA: Link Chart Title to Drop Down Menu | Excel Programming |