your days with Lotus Symphony
Good memory, Fred. I barely remember those days!<g>
--
Jim
| Hi Jim and Dave,
|
| Thank you for taking the time to help me out, I really appreciate it.
|
| Fred
| PS Jim, I've been watching you help people since your days with Lotus
| Symphony. Do you have a proper job? (grin)
|
| "Jim Rech" wrote:
|
| > I'd guess you're missing a reference to "Microsoft Office XX Object
Library"
| > under Tools, References in the VBE.
| >
| > --
| > Jim
| > | > | Hi JP,
| > |
| > | I don't know if it's possible to attach files to these posts so here
is
| > the
| > | code lonhand:
| > |
| > |
| > |
| > | Sub AddMenus()
| > | Dim cMenu1 As CommandBarControl
| > | Dim cbMainMenuBar As CommandBar
| > | Dim iHelpMenu As Integer
| > | Dim cbcCutomMenu As CommandBarControl
| > |
| > | '(1)Delete any existing one. We must use On Error Resume next _
| > | in case it does not exist.
| > | On Error Resume Next
| > | Application.CommandBars("Worksheet Menu Bar").Controls("&New
Menu").Delete
| > | On Error GoTo 0
| > |
| > | '(2)Set a CommandBar variable to Worksheet menu bar
| > | Set cbMainMenuBar = _
| > | Application.CommandBars("Worksheet Menu Bar")
| > |
| > | '(3)Return the Index number of the Help menu. We can then use _
| > | this to place a custom menu before.
| > | iHelpMenu = _
| > | cbMainMenuBar.Controls("Help").Index
| > |
| > | '(4)Add a Control to the "Worksheet Menu Bar" before Help.
| > | 'Set a CommandBarControl variable to it
| > | Set cbcCutomMenu = _
| > | cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
| > | Before:=iHelpMenu)
| > |
| > | '(5)Give the control a caption
| > | cbcCutomMenu.Caption = "&New Menu"
| > |
| > | '(6)Working with our new Control, add a sub control and _
| > | give it a Caption and tell it which macro to run (OnAction).
| > | With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
| > | .Caption = "Menu 1"
| > | .OnAction = "MyMacro1"
| > | End With
| > | '(6a)Add another sub control give it a Caption _
| > | and tell it which macro to run (OnAction)
| > | With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
| > | .Caption = "Menu 2"
| > | .OnAction = "MyMacro2"
| > | End With
| > | 'Repeat step "6a" for each menu item you want to add.
| > |
| > |
| > | 'Add another menu that will lead off to another menu
| > | 'Set a CommandBarControl variable to it
| > | Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
| > | ' Give the control a caption
| > | cbcCutomMenu.Caption = "Ne&xt Menu"
| > |
| > | 'Add a contol to the sub menu, just created above
| > | With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
| > | .Caption = "&Charts"
| > | .FaceId = 420
| > | .OnAction = "MyMacro2"
| > | End With
| > |
| > |
| > |
| > | End Sub
| > |
| > | Sub DeleteMenu()
| > | On Error Resume Next
| > | Application.CommandBars("Worksheet Menu Bar").Controls("&New
| > Menu").Delete
| > | On Error GoTo 0
| > | End Sub
| > |
| > | Thanks for your interest.
| > | Fred
| > |
| > | "JP" wrote:
| > |
| > | > Can you post the code?
| > | >
| > | > --JP
| > | >
| > | > On Mar 4, 5:12 am, Fred Davis <
[email protected]>
| > | > wrote:
| > | > > Hi guys,
| > | > >
| > | > > I have just created a brand new spreadsheet and cut and pasted the
| > offending
| > | > > code into it. Guess what - it works!
| > | > >
| > | > > So, what is missing from my old spreadsheet that is stopping the
code
| > | > > working?
| > | > >
| > | > > Re-building the old spreadsheet under my current version is
definitely
| > a
| > | > > 'LAST RESORT'. I don't want to even think about it!
| > | > >
| > | > > Fred
| > | > >
| > | > >
| > | >
| >
| >
| >
