MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Rayn on May 29, 2001 8:17 PM


i have created 2 macros one to protect formulas and another
to unprotect them. i am working with 20 different workbooks.
what i would like to is add these macros to my tools menu, which
will make avoid haveing to insert them in idvidual workbooks.
How can i make these macros become a default in excel.
so they can be there as an addon.

Thank you for your assistance

Posted by Dax on May 30, 2001 2:15 AM

If you include the macros in your personal workbook they will be available at all times. If you click Window, Unhide you should see the Personal workbook. If not, record a macro and select the destination to be Personal Macro Workbook.

This file is loaded automatically every time you open Excel so you could assign a button or menu option to the macros.


Posted by Dave Hawley on May 30, 2001 5:19 AM

Hi Rayan

If you place the code below in the Workbook module of your personal workbook, it will add two command bar buttons automatically. Just change the Text and Onaction (your macro names) to suit.

Dim Cbar As CommandBarPopup
Dim cBut1 As CommandBarButton
Dim cBut2 As CommandBarButton

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then Exit Sub
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&OzGrid").Delete
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&OzGrid").Delete
Set Cbar = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
Cbar.Caption = "&OzGrid"
Set cBut1 = Cbar.Controls.Add(Type:=msoControlButton)
With cBut1
.Caption = "My Macro1"
.Style = msoButtonCaption
.OnAction = "MyMacro1"
.FaceId = 26
End With

Set cBut2 = Cbar.Controls.Add(Type:=msoControlButton)
With cBut2
.Caption = "My Macro2"
.Style = msoButtonCaption
.OnAction = "MyMacro2"
End With

End Sub

OzGrid Business Applications