Menu problem

M

michael.beckinsale

Hi All,

The code snippet below is supposed to put a custom menu on the Excel
menu bar. I have used it many times before without a problem. I have
other files with the same coding that open in my current version of
Excel 2003 which open and insert the menu without a problem.

In this particular workbook it throws up the following error

'User defined type not defined'

and greys out 'CommandBarControl' but surely this is defined using the
Set statement?

What can cause this in this specific workbook and not others?

There is code within this workbook that gets data from Outlook but
apart from that l dont beleieve there is anything special about it.

I have checked the references and everything appears to be OK.

Private Sub Workbook_Open()

Call DeleteMenu

Dim michaelsmenu As CommandBarControl
Set michaelsmenu = Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Before:=8)
michaelsmenu.Caption = "&CARTEK"
michaelsmenu.BeginGroup = False
michaelsmenu.Visible = True

Dim mymenu1 As CommandBarControl
Set mymenu1 = michaelsmenu.Controls.Add(Type:=msoControlButton)
mymenu1.Visible = True
mymenu1.Style = msoButtonCaption
mymenu1.Caption = "Get unsubscribers and remove"
mymenu1.OnAction = "ListUnsubscribers"

Dim mymenu2 As CommandBarControl
Set mymenu2 = michaelsmenu.Controls.Add(Type:=msoControlButton)
mymenu2.Visible = True
mymenu2.Style = msoButtonCaption
mymenu2.Caption = "Remove specific email address"
mymenu2.OnAction = "emailremove"

All help gratefully received

Regards

Michael Beckinsale
 
M

MattShoreson

This is part of a menu generator I use.

Dim cbmMainMenu As CommandBarPopup
Dim cbmSubMenu As CommandBarPopup
Dim cbmSecondSubMenu As CommandBarControl

On Error GoTo HandleErr

With Application.CommandBars(1).Controls

Set cbmMainMenu = .Add(Type:=msoControlPopup, Temporary:=True)

With cbmMainMenu
.Caption = "&" & cstrMENU
.Tag = cstrMENU

Set cbmSecondSubMenu
cbmMainMenu.Controls.Add(msoControlButton)

With cbmSecondSubMenu
.Caption = "&Prepare Barra Upload"
.OnAction = "Prepare"
End With

Set cbmSecondSubMenu
cbmMainMenu.Controls.Add(msoControlButton)

With cbmSecondSubMenu
.Caption = "&Generate Excel Report"
.OnAction = "GenReport"
End With

Set cbmSecondSubMenu
cbmMainMenu.Controls.Add(msoControlButton)

With cbmSecondSubMenu
.Caption = "&Set File Paths"
.OnAction = "SetFilePaths"
.BeginGroup = True
End With

End With

End With

ExitHere:
Exit Sub

HandleErr
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top