XL97: Sample Macros to Customize or Replace a Menu Bar

• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, menu bars and toolbars are referred to as the same programmable object type (a command bar). You can use Visual Basic for Applications to manipulate the built-in command bars in Microsoft Excel 97. This article contains the following macro examples that customize or replace the built-in Worksheet menu bar:

• Adding a custom command bar to the built-in Worksheet menu bar
• Creating a custom command bar item
• Deleting a custom command bar item
• Creating a new command bar
• Replacing the built-in Worksheet command bar
• Restoring the built-in Worksheet command bar
• Deleting the custom command bar

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Adding a Custom Command Bar to the Built-in Worksheet Menu Bar

The following subroutines add a command bar named "Test" to the worksheet menu bar for all sheets in the active workbook. The examples use identification numbers for built-in and custom command bar controls. The identification number for a particular control is used as an argument for the Add method.

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q159466
   TITLE     : XL97: List of ID Numbers for Built-In Command Bar Controls

   ARTICLE-ID: Q159619
   TITLE     : XL97: Sample Macros to Control Menus and Submenus

Creating a Custom Command Bar Item

The following subroutine creates a custom command item:

   Sub CustomBar()

       Dim newpopup As Object
       Dim newmenu As Object

       Set newpopup = Application.CommandBars("worksheet menu bar") _
           .Controls.Add(msoControlPopup)

       Set newmenu = newpopup.Controls.Add(msoControlPopup, 1)

       With newmenu
           .Caption = "My New Menu"
           .Controls.Add Type:=msoControlButton, Id:=2949, before:=1
           .Controls(1).Caption = "Button 1"
           .Controls(1).OnAction = "Button_1"
       End With

       With newpopup
           .Caption = "&Test"
           .Controls.Add Type:=msoControlButton, Id:=2949, before:=2
           .Controls(2).Caption = "Button 2"
           .Controls(2).OnAction = "Button_2"
           .Controls.Add Type:=msoControlButton, Id:=2950, before:=3
           .Controls(3).Caption = "Button 3"
           .Controls(3).OnAction = "Button_3"
           .Controls.Add Type:=msoControlButton, Id:=2949, before:=4
           .Controls(4).Caption = "Delete this Menu"
           .Controls(4).OnAction = "Delete_menu"
        End With

   End Sub

'The following message boxes are used by each button on the new command bar
' named "&test"

   Sub button_1()
       MsgBox "You Clicked Button 1!"
   End Sub

   Sub button_2()
       MsgBox "You Clicked Button 2!"
   End Sub

   Sub button_3()
       MsgBox "You Clicked Button 3!"
   End Sub

Deleting a Custom Command Bar Item

The following subroutine deletes a custom command bar item:

   Sub Delete_menu()

       For Each Bar In CommandBars("worksheet menu bar").Controls
           If Bar.Caption = "&Test" Then Bar.Delete
       Next Bar

   End Sub

Creating a New Command Bar

The following subroutine creates a custom command bar:

   Sub New_Command_Bar()

       Dim newbar As Object
       Dim newbar2 As Object

       Application.CommandBars.Add "My command bar", , True

       Set newbar = Application.CommandBars("My command bar") _
           .Controls.Add(msoControlPopup, Before:=1)

       With newbar
           .Caption = "&File"
           .Controls.Add(msoControlButton, Id:=2520, _
               Before:=1).Caption = "New"
           .Controls.Add(msoControlButton, Id:=23, _
               Before:=2).Caption = "Open"
           .Controls.Add(msoControlButton, Id:=3, _
               Before:=3).Caption = "Save"
           .Controls.Add(msoControlButton, Id:=2949, Before:=4).Caption = _
               "Restore The Worksheet Menu Bar"
           .Controls("Restore the Worksheet Menu Bar").OnAction = _
               "Restore"
       End With

       Set newbar2 = Application.CommandBars("My command bar") _
           .Controls.Add(msoControlPopup, Before:=2)

       With newbar2
           .Caption = "&My Edit"
           .Controls.Add Type:=msoControlButton, Id:=2949, Before:=1
           .Controls(1).Caption = "Button 1"
           .Controls(1).OnAction = "button"
       End With
   End Sub

'The following sub-routine is run when button 1 is clicked from the Edit
'command bar.

   Sub button()

       MsgBox "You Clicked Button 1"

   End Sub

Replacing the Built-in Worksheet Command Bar

The following subroutines replace the built-in Worksheet menu bar with a custom menu bar:

   Sub ShowNewBar()

       Application.CommandBars("My command bar").Visible = True

   End Sub

Restoring the Built-in Worksheet Command Bar

The following line of code restores the built-in Worksheet menu bar:

   Sub restore()
       Application.CommandBars("My command bar").Visible = False
   End Sub

Deleting the Custom Command Bar

The following subroutine deletes a custom command bar:

   Sub DeleteNewBar()

       Application.CommandBars("My command bar").Delete

   End Sub

REFERENCES

For more information about command bars, click the Index tab in Microsoft Excel 97 Help, type the following text

   Command Bar