Adding Menu Item to Worksheet Menu Bar

mhabib · May 25th 2005

Hi,

I'd like to Add a new menu item to the Worksheet Menu Bar. The caption on the new menu should be Cost Statement. Within the new menu item, I'd like to add the following buttons.

First Button:
Caption: Reset Report
Macro: ResetAll

Second Button:
Caption: Locate Missing IDs
Macro: UnmatchedListing

The new menu item needs to be added in when the ThisWorkbook opens, and the worksheet menubar is to be reset each time ThisWorkbook closes.

Any help would be highly appreciated.

Thanks.

m

PS: ThisWorkbook is a normal excel file (xls) - Not an Add-in

**norie** · May 25th 2005

Re: Adding Menu Item to Worksheet Menu Bar

The best way I've found to start on something like this is to turn on the macro recorder and do it manually.

Then you can alter the generated code if needed.

mhabib · May 25th 2005

Re: Adding Menu Item to Worksheet Menu Bar

Thanks for the suggestion, Norie.

I've already tried the record-and-run approach. Unfortunately, I get an illegal operation error when I try to run the recorded code.

I have the option of creating a custom toolbar and attaching it to the workbook. However, I'd like to generate the commandbar programmatically so that the user cannot simply delete/alter its settings.

m

**norie** · May 26th 2005

Re: Adding Menu Item to Worksheet Menu Bar

Can you post the code that was generated by the macro recorder?

dacat1997 · May 26th 2005

Re: Adding Menu Item to Worksheet Menu Bar

I believe you are asking about the tool bars but i'm attaching this zip file...it has some code to add a custom menu. I found it somewhere in this forum but can remember where.

mhabib · May 26th 2005

[Solved]Re: Adding Menu Item to Worksheet Menu Bar

Thanks for the help guys!

dacat1997: You Rock! Thanks to you, I have a fighting chance to deliver my project on time.

Norie: My problem's solved. But here's the code that was generated when I recorded the macro to add a new menu to the Worksheet Menu Bar. FYI, it creates a new menu item, but does not take up the caption entry made during the recording process.

Code

Sub AddMenuItem()
'
' AddMenuItem Macro
' Macro recorded 26/5/2005 by M A Habib
'


'
    Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
        msoControlPopup, Before:=11
End Sub

Display More

A million thanks once again.

m

rbrhodes · May 26th 2005

Re: Adding Menu Item to Worksheet Menu Bar

Hi,

Here's code to put a menu on the standard menu bar when the workbook is opened - complete with two buttons (ctrl1 and ctrl2), and delete the menu item when the workbook is closed. (I think that's what you wanted!)

Some info I got from the help files, some from a book but mostly from C. Pearsons' pages at: http://www.cpearson.com/excel/menus.htm His web site is invaluable if you're trying to learn VBA!

These two go in the 'This Workbook' section:

Code

Private Sub Workbook_Open()
  
Dim mymenubar As CommandBar
Dim newmenu As CommandBarPopup
Dim ctrl1, ctrl2 As CommandBarButton
   
Set mymenubar = Application.CommandBars("Worksheet menu Bar")


'Change  before:= "8" for placement of the menu...


Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=8)


'This is the title of the new menu item


newmenu.Caption = "Cost Statement"


Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, Id:=1)


'Caption is the "title", Tooltip is "what you see if you hover the mouse
' over the button", and Tag is a "search item" (not needed really...)
' in the Onaction line isthe name of the macro to run.  In this case it is
' the word resetall  Keep the  "'!     "    around the macro name!
    With ctrl1
        .Caption = "Reset Report"
        .TooltipText = " put something here for the 'Tool Tip'"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!resetall"
        .Tag = "SomeString1"
    End With
        
Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, Id:=1)


    With ctrl2
        .Caption = "Locate Missing Id's"
        .TooltipText = " put something here for the 'Tool Tip'"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!unmatchedlisting"
        .Tag = "SomeString1"
    End With
        
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl


Set CmdBar = Application.CommandBars("Worksheet Menu Bar")


'This line must have the Caption of the menu item in it...
Set CmdBarMenu = CmdBar.Controls("Cost Statement")


CmdBarMenu.Delete


End Sub

Display More

And these two are just example macros (named as you asked) for anyone else who may want to try this code "as is". Put them in a standard module for the code to work "as is". Or edit the above code to run your own macros. (You can also change the menu name, item names, etc., of course - see the comments inside the macros.

Code

Sub resetall()
    MsgBox "resetall"
End Sub
Sub unmatchedlisting()
    MsgBox "unmatchedlisting"
End Sub

Cheers,

dr

Adding Menu Item to Worksheet Menu Bar

Participate now!

Share