Free Microsoft Excel 2013 Quick Reference

can't create a menu item to execute addin

I have a workbook with one macro. I would like to convert this macro into an addin.
Saving it as a xlam file was the easy part.
Now I need to add a button to the menu bar to be able to execute the addin.

What would be the easiest way to do that?

Post your answer or comment

comments powered by Disqus
I have created a custom menu.

Could somebody please tell me how I can make a menu item be checked or not checked (a check mark next to the menu item).

Sub Load_Menu()

Dim mMain As CommandBarPopup, mSub As CommandBarPopup
Dim SubMenu As CommandBarPopup, cmbBar As CommandBar
Dim FaceID As Integer

' start by deleting the mainmenu if it already exists
'Worksheet Menu Setup
  Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
  Set mMain = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
  With mMain
      .Caption = "&Migration"
      .Tag = "Migrate"
  End With
'Worksheet Menus
  CreateMenuItem mMain, "Auto Fill Time Column", "AutoFillTimeCol", False, 23
    ' free memory
    Set mSub = Nothing
    Set mMain = Nothing
End Sub

Sub DeleteMenu()
    Dim mMain As CommandBarControl
    Set mMain = Application.CommandBars(1).FindControl(Tag:="Migrate")
    If Not mMain Is Nothing Then mMain.Delete
End Sub
Sub CreateMenuItem(CtrlName, CapName, MacName, Group, FaceID)

    With CtrlName.Controls.Add(Type:=msoControlButton, temporary:=True)
        .BeginGroup = Group
        .Caption = CapName
        .OnAction = MacName
        .Style = msoButtonIconAndCaption
        .FaceID = FaceID
    End With

End Sub

Sub CreateSubItem(SubCtrlName, CapName, MacName, FaceID)
    With SubCtrlName.Controls.Add(Type:=msoControlButton, temporary:=True)
        .Caption = CapName
        .OnAction = MacName
        .Style = msoButtonIconAndCaption
        .FaceID = FaceID
    End With

End Sub

Hi all,
I created a custom menu in Excel. I would like to create sub menu items for
one of the menu items. I get an error 'Object not supported by this method'.

I have copied the code below: any suggestions why the error pops up?

Thanks in advance for any pointers.

the code:

Private Sub Workbook_Open()

Dim obj As Object
Dim helpmenu As Object
Dim btnobj As Object
Dim SubMenuItem As Object

For Each obj In Application.CommandBars(1).Controls
If obj.Caption = "De&mo Tool" Then
Exit For
End If
Set obj = Application.CommandBars(1).Controls.Add(msoControlPopup)
obj.Caption = "De&mo Tool"

Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "&Save data to file"
btnobj.OnAction = ThisWorkbook.Name & "!store"
btnobj.FaceId = 600
Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "F&ilter"
btnobj.FaceId = 601
Set SubMenuItem = btnobj.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = "&Asia"
SubMenuItem.OnAction = "Macrofilterasia"
End Sub

With regards,

I have created an Addin from one I found on this site which creates a menu on the CommandBar. On this menu I can add my Macros. The Menu is initially supposed to perform a delete function then an add, just in case the menu already exists on the CommandBar. It is also supposed to Uninstall the menu when you de-select the Addin. At the moment it doesn't seem to be doing either, as I have now got 5 CommandBar menu's all the same and I can't delete them?

This also creates a problem of when I add another macro, the menu on the CommandBar doesn't update with the addition?

I have attached the .xla file for you to look at and see where I'm going wrong.

VBA Macros & Creating An Add-in For Them


I am wondering if there is any code to SELECT (DESELECT) A MENU ITEM using VBA?

I have an Excel Add-in program installed, which adds a Menu "Easycal" to my Excel menu bar.
Under "Easycal" (drop down list), I have the following items:

Refresh workbook
Refresh worksheet
Turnoff Easycal

I am using "Easycal"'s functions in various cells of my sheet "Final". When I select "Refresh worksheet" from "Final", "Easycal" fetches (or calculates) me the data from the "Easycal" database.

I have a macro (on "Final") that pulls together data from other sheets of the same workbook.

I there anyway to include a code in my existing macro, so that when I run the macro, the following are completed in sequence:

1. Select "Turnoff Easycal" menu item
2. Run my existing macro (to pull the data from other sheets to "Final")
3. DeSelect "Turnoff Easycal" menu item
4. Select "Refresh worksheet" menu item


I've been playing with a BeforeDoubleClick event. I've set Cancel = True at the end of the code, for neatness. However, this obviously means that double-click to edit isn't available for any of the cells where my code won't run.

So I was thinking about having a menu item to switch the event on only when I wanted it - fairly straightforward, but there's no visual clue as to whether the event is on or off. A tick by the menu item to indicate enabled would be great - but I can't work out how to do it. Is there an easy way?


I want to create a pivot table as below to compare sales numbers by product
with 2 years in the columns, and for each year the kg sold, the margin and
the margin/kg. Furthermore I want to calculate the difference between the 2
years for these 3 fields (kg, margin & margin/kg). I have created a
calculated field for the margin/kg.
For the difference between the 2 years, I have created a calculated item.
This is OK for the kg sold and the margin, but for the margin/kg, it gives
the wrong output (it calculates the margin/kg of the difference instead of
the the difference of the margins/kg).
In my example below, the pivot table gives for most right column 3,33, while
it should be -5 (= 15-20)

2008 2007
kg margin margin/kg kg margin margin/kg kg
margin margin/kg
product A 10 150 15 7 140 20
3 10 3,33

Thanks in advance

in a excel file, how to make a menu item for the .xls file that when clicked
on it runs example plz

Example is on:

"Daniel" <> schreef in bericht
> in a excel file, how to make a menu item for the .xls file that when
> clicked
> on it runs example plz

I'm a VBA beginner trying to pick up some tips quickly to simplify some
company tasks. We have employees enter information into a worksheet
and I'm trying to create a step-by-step procedure that's as dummy-proof
as possible (judging from the mistakes people are making, it's
essential). I'm fine with assigning macros to buttons inserted into a
page, but one thing I can't figure out how to do is add a very basic
menu item to a button that sits within the sheet.

For example, I need for the employees to insert a hyperlink to a
network file into a specific cell (the file name and network location
will change every time). So I want users to push a button in the
spreadsheet that says "Insert Hyperlink," let them browse to the
appropriate file and then have it inserted into the cell. This is the
same result they'd get by placing the cursor in the cell and hitting
Ctrl-K. I can't record the macro without selecting a specific file.

I"m sure this is painfully easy but I can't find anything. Can someone
out there take pity on a VBA newbie?


I would like to create a menu with up to 10 options and have the ability to
accept input of a number from the menu which corresponds to one of the menu
I have just started using my Excel 97 so as much detail as possible would be
Thank You

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?



I'd like to Add a new menu item to the Worksheet Menu Bar. The caption on the new menu should be Cost Statement. Within the new menu item, I'd like to add the following buttons.

First Button:
Caption: Reset Report
Macro: ResetAll

Second Button:
Caption: Locate Missing IDs
Macro: UnmatchedListing

The new menu item needs to be added in when the ThisWorkbook opens, and the worksheet menubar is to be reset each time ThisWorkbook closes.

Any help would be highly appreciated.



PS: ThisWorkbook is a normal excel file (xls) - Not an Add-in

Create Custom Menu Items
It is possible to customize Excel in many different ways. However, to make your Excel spreadsheets have that professional look and feel, one can add their own custom menus to an existing Excel toolbar.

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?


I've seen this addressed a number of times but have yet to see a
satisfying answer - how do we add a persistent Excel toolbar menu item
from a Automation Add-in?

I've built an Automation Add-in that also implements the optional
IDTExtensibility2 interface (in order to take advantage of start up
events). I create a menu item from the OnConnection() method. Here's
what happens:

1) Run Excel.
2) Begin to insert a formula. (OnConnection() doesn't fire until I
enter the first character after the equal sign (e.g., =A))
3) OnConnection runs and installs my menu item.
4) Menu item remains on the toolbar while Excel runs.
5) Save the spreadsheet, quit out of Excel.
6) Run Excel, open saved spreadsheet, menu item is gone...

I'm looking for my menu item to be persistent and to be displayed
either when I start Excel, or when I open a previously saved
spreadsheet from which the menu item had been installed.

What's the proper way to achive this behaviour? (FWIW, I'm using Excel
2003 and C#/.NET)


How can I add my own menu item to the right click context menu.

When user clicks on a cell and right clicks; I want my own menu item to be
seen - so that I can run a macro, when user clicks that menu and also wants
to disable that menu for some cells...

Any help is really appreciated!!

Thanks in advance!!

Hi folks,

I am building a toolbar to be used by staff in my office to automate certain
Excel functions.

I am looking to build a sheet which I will send to the staff, which they
will open once and this will load the toolbar into their Excel and save the
toolbar, meaning that it should appear whenever they open Excel from now on.

The problem I am having is that I cannot create a menu option at the top of
the Excel page (what I want is for a menu item to be created at the top named
'Toolbar', when they click this the toolbar loads into Excel).

This menu works fine for me just now, however I cannot create this using VBA
so that I can send a sheet to every team member to create the toolbar?

Any thoughts?

Any help appreciated.


I have been working with Steven Roman's, 'Writing Excel Macros with VBA' 2nd
Edition. The code below adds the Custom popup menu to the worksheet menu. The
code does not contain a Temporary:=True statment when the control was added
to the menu. Now I alway have a Custom popup menu when I launch Excel.

Before I add the Temporary:=True to the Controls.Add line of code I want to
delete the existing Custom menu. Can I get some help on how I can ID the
current Custom menu item? No Tag was set and I don't know how to determine
the controls ID so I can use it for the purpose of deleting it.

Thanks in advance


Sub CreatePopup()
' Example 12-5 pg 149

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbsub As CommandBarControl

' Create a popup control on the main menu bar
Set cbpop = Application.CommandBars("Worksheet Menu Bar"). _
cbpop.Caption = "&Custom"
cbpop.Visible = True
' Add a menu item
Set cbctl = cbpop.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
' Next is required for caption
cbctl.Style = msoButtonCaption
cbctl.Caption = "MenuItem&1"
' Action to perform
cbctl.OnAction = "ExampleMacro1"
' Add a popup for a submenu
Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
cbsub.Visible = True
cbsub.Caption = "&SubMenuItem1"
' Add a menu item to the submenu
Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
' Next is required for caption
cbctl.Style = msoButtonCaption
cbctl.Caption = "SubMenuItem&2"
' Action to perform
cbctl.OnAction = "ExampleMacro2"

End Sub


I'm tryn to add a command button the the menu called "Dodatki"
So it's just a command button not like it's now a menu under "Dodatki" and in this menu there's a command button that says "Shrani naročilo v .pdf"
Is it possible to have a large button under Menu "Dodatki" withouth a submenu?
I'm using this code but I don't know how to fix it.
Private Sub Workbook_Open()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "&Naročila" 'names the menu item
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Shrani Naročilo v .pdf"
            .OnAction = "RunMyMacro1" 'runs the specified macro
            .FaceId = 1098 'assigns an icon to the dropdown
        End With
    End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("Naročila").Delete 'delete the menu item
End Sub
Hope some understands my question.
And I can't attach the attachment :/


How can I create a horizontal link to a vertical cells in another work sheet?

I have sheet A and sheet B.

Sheet B has a column.
I want this to create a link to Sheet B in Sheet A - I can do this for individual cells, by doing:


for example.

But: how do I copy one column and and apply it a row in the other spreadsheet?

I've read up, and can do this for values by using 'Past Special' and choosing 'Transpose'.

I don't cant to copy just the values: I want to create a link, so that if cells change in SheetB, then they change in SheetA.

Apart from doing this manually: I can't figure out how else to do it.



How do i create a menu that you can click and it will take you to a selected sheet?

so for example:

sheet 1 shows


if i click cars it will take me to sheet 2 bikes sheet 3 ect

thanks guys



I started trying to answer a thread (Printing specific pages in a report) but it has changed into trying to get a menu item to appear.

The problem is Originally Posted by Simpsonc2 I have opened the form in design view. There is no toolbar or toolbox icon, I select View: and the toolbox option is greyed out and I cannot select it. It's a mystery? In the thread I establish that he has not disabled design rights.

Any ideas?



I posted a question about this problem back in 2005, but never provided code. After a bit of thought, I decided the post was too old to renew so am starting a new thread.

(later edit) This may be a bit much for anyone to tackle, but the macros have received significant acclaim where I work. They import a bunch of data and build formated charts ready to show. As written they import and chart tracking antenna log data. If you do look at them, please respond and let me know what you think. (even if you have no suggestions, I would like to hear what others think of this body of work. If you do like them, please do not widely dissiminate until after ITC conference in October)

The macros add a menu item to call some macros that build charts. When the data worksheet is displayed, the menu item is displayed. When a chart is displayed, the menu item is not displayed. What can possibly cause this?

The macros can be downloaded from
Select the option ViaSat Macros Workbook to download the workbook. It contains data to chart and and the size is 7,069,696 as seen when I just downloaded it. To initially display the menu run the macro Add_ViaSat_Menu. This will add the menu item ViaSat Macros The menu is not permanent. When the menu is displayed, select it then select Build_ViaSat_Charts. From that menu, select any of the charts. I suggest the top left one. Then select Build. The chart will be displayed and the menu item ViaSat Macros will disappear. To display the menu item, select the worksheet data or notes.

An almost complete user’s guide is on the web site. As posted, the macros contain the data. The guide tells how to import the data so there is no need to import.

The question: Why does the menu item hide when a chart is displayed?

(edited to fix a typo, then add a request)

Where in the Excel menus is the menu item to allow changing options for the
file format converters? For example, to change the maximum line length for
the ".prn" file converter.

No luck finding an answer? You could always try Google.