Solved

How to Customize Excel menu bar at run time?

Posted on 2002-03-07
2
152 Views
Last Modified: 2010-05-02
Hello all
I need to add items to excel menu bar at runtime
and to load this menu as an Addin.

I prefer to see code that create a new menu item
(like file,edit,tools etc.) and add to it items
and sub menus with connection to event handlers

Thanks
0
Comment
Question by:avi9260
2 Comments
 
LVL 3

Accepted Solution

by:
nzjonboy earned 70 total points
ID: 6848900
check this out

Sub Auto_Open()
  Dim myBlankPop As CommandBarPopup
  Dim myBlankSubPop As CommandBarPopup
  Dim myBlankBtn As CommandBarButton

  Set myBlankPop = CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, , , 11, True)
 
  With myBlankPop
    .Caption = "Test"
 
    Set myBlankBtn = .Controls.Add(msoControlButton)
 
    With myBlankBtn
      .Style = msoButtonIconAndCaption
      .Caption = "Test Button"
      .FaceId = 480
      .OnAction = "clickBtn"
    End With
   
    Set myBlankBtn = Nothing
   
    Set myBlankSubPop = .Controls.Add(msoControlPopup)
   
    With myBlankSubPop
      .Caption = "Sub Menu"
      Set myBlankBtn = .Controls.Add(msoControlButton)
      With myBlankBtn
        .BeginGroup = True
        .Style = msoButtonIconAndCaption
        .Caption = "Test Button"
        .FaceId = 1845
        .OnAction = "clickSubBtn"
      End With
      Set myBlankBtn = Nothing
    End With
   
    Set myBlankSubPop = Nothing
       
  End With
End Sub

Function clickBtn()
  MsgBox "click"
End Function

Function clickSubBtn()
  MsgBox "click from sub menu"
End Function


hope this helps

nzjonboy
0
 

Author Comment

by:avi9260
ID: 6849932
Thanks a lot
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question