Tuesday, July 14, 2009

How to Create New Menu using Excel VBA

How to automatically add menu to Excel using VBa

Here is a code to add a new menu and a button whenever a sheet is opened.

Option Explicit
Private Const APPNAME As String = "Sample Menu"
Private Sub Workbook_Open()



Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim vID

On Error GoTo Err_DUD

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(APPNAME).Delete
On Error GoTo 0

Set ctlNewMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
ctlNewMenu.Caption = APPNAME

'--- Add Control Buttons ---------------
Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)
ctlNewItem.Caption = "Sample Button"
ctlNewItem.OnAction = "SayHello"
ctlNewItem.TooltipText = "Sample Button by VBADUD"

Err_DUD:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub



5 comments:

  1. Anonymous4:10 PM

    Where is macro "SayHello"?

    ReplyDelete
  2. Hi
    Once it is added, how do I remove it?

    ReplyDelete
  3. Anonymous4:07 PM

    perfect!
    but, how assign name to tabs?
    thanks so much

    ReplyDelete
  4. BASANT BALLABH JOSHI ALMORA KUMOULI11:49 PM

    PL. GIVE THE FULL EXAMPLE HOW MAKE A MENU FOR RUNNING .VBS FILE

    ReplyDelete
  5. just-me,

    In menu area, right click on button you want to remove, than choose "Delete Custom Command". That's all.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group