creating my own menu item in excel

  • Thread starter Thread starter ismailasraf
  • Start date Start date
I

ismailasraf

I want to replace existing menu (File, Edit,........ Help) with my own
menu for my simple accounting application using macros. on closing the
application the system menu should be restored.
 
Dear Asraf

The below two procedures one for Adding menus and one for removing menus
should help. Please try and post back for any further help

If this post helps click Yes
---------------
Jacob Skaria

Sub AddMenus()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Tool-&Kit").Delete
On Error GoTo 0

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = cbMainMenuBar.Controls("Help").Index
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=iHelpMenu)
cbcCutomMenu.Caption = "Tool-&Kit"
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Consolidate open sheets"
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Start from Row 1"
.OnAction = "ConOpenSheets1"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Start from Row 2"
.OnAction = "ConOpenSheets2"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Specify Row"
.OnAction = "ConOpenSheets3"
End With

End Sub

Sub DeleteMenu()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Tool-&Kit").Delete
On Error GoTo 0

End Sub
 
Back
Top