VBA TIPS
Making a menu in the Tools menu

How to make an add-in is so simple. When you save a workbook file, you can make an add-in file for the saving as Microsoft Excel Add-in(*.xla). That's it!....But you might feel, how can you perform the procedures in the add-in?

Example

Yes, to perform a procedure, we need to create a menu for making a procedure run. Here is an easy sample for making a menu in the Tools menu.(shown in the picture below)



In this case, all procedures must be placed in a standard module. If you've known Workbook_Open event and Workbook_BeforeClose event, you can write these procedures in the Thisworkbook Module instead of Auto_Open and Auto_Close.

But please note, in the version of Excel 2000, there is a bug that Workbook_BeforeClose event will not work in an add-in file. So I would reccomend you to use Auto_Close procedure.

When you cannot delete your custom menu in the Tools menu, don't worry! you can Reset the Tools menu anytime you want.
Type Application.CommandBars.FindControl(ID:=30007).Reset in the Immediate Window then press [Enter] key.



Place the following in a standard module. After saving this workbook then reopen the workbook again.

Option Explicit

Public Const Nm As String = "My Excel Add-in"


Sub Auto_Open()
'This procedure would be performed when the workbook is opened.
    Call CreateBar
End Sub


Sub Auto_Close()
'This procedure would be performed when the workbook is closed.
    Call DeleteBar
End Sub


Private Sub CreateBar()
    Dim Bar As CommandBarPopup
    Dim BarBtn As CommandBarButton
    Dim i As Integer

    'Just in case Delete menu
    DeleteBar
    'Set Tools menu as an object variable
    Set Bar = _
    Application.CommandBars.FindControl(ID:=30007).Controls.Add(msoControlPopup)
    With Bar
        .BeginGroup = True    'Add a line for partition
        .Caption = "&Converter addin"
        .Tag = Nm
        For i = 1 To 2
            Set BarBtn = .Controls.Add(msoControlButton)
            With BarBtn
                'Change here to change the caption on the menu
                .Caption = Choose(i, "Give it a try #1", "Give it a try #2")
                'Change here to change the procedure that shoud be called
                .OnAction = Choose(i, "Test", "Test2")
                .FaceId = Choose(i, 2892, 487)    'Use face ID
                .BeginGroup = Choose(i, FalseFalse)
            End With
        Next
    End With
End Sub


Private Sub DeleteBar()
    On Error Resume Next
    Application.CommandBars.FindControl(Tag:=Nm).Delete
    On Error GoTo 0
End Sub


Sub Test()
    MsgBox "Works?"
End Sub


Sub Test2()
    MsgBox "Works well?"
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo