Customising Menu bar with Visual basic

[IanD_1]

How do I replace the Menu Bar in Excel with a customised Menu using VB.

 

[HaHoBe]

Hi, Ian,

I don´t like the idea very much:

'ClassModule: ThisWorkbook
Option Explicit

Private Sub Workbook_Open()
' Hans W. Herber, 011898
   Dim oBar As CommandBar
   Dim oButton As CommandBarButton
   On Error Resume Next
   Application.CommandBars.Add("My Commandbar").Delete
   On Error GoTo 0
   Set oBar = Application.CommandBars.Add("My Commandbar", _
      MenuBar:=True, temporary:=True)
   Set oButton = oBar.Controls.Add
   With oButton
      .Caption = "My Button"
      .Style = msoButtonCaption
   End With
   Application.CommandBars("My Commandbar").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   On Error Resume Next
   Application.CommandBars("My Commandbar").Delete
   On Error GoTo 0
   Application.CommandBars("Worksheet Menu Bar").Visible = True
End Sub

Ciao,
Holger

 

[shades]

Also, look at chapters 22-23 in Power Programming with VBA by John Walkenbach. Also, if you go this route, then consider the book Professional Excel Development, by Stephen Bullen and others, even world class programmers regularly consult this book. It provides an overall integrated approach to doing something like this - there are some pitfalls that you have be aware of. And I am sure that Holger reflect his hesitation because of these pitfalls.
________
Toyota nz engine