Customising Menu bar with Visual basic

  • Thread starter Thread starter IanD_1
  • Start date Start date
I

IanD_1

Guest
How do I replace the Menu Bar in Excel with a customised Menu using VB.
 
Hi, Ian,

I don´t like the idea very much:

Code:
'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
 
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
 
Last edited:

Users who are viewing this thread

Back
Top Bottom