Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have an oddity with trying to enhance an existing workbook by adding a
menu. If I open a fresh Excel 2003 workbook, open VBA, insert a module, and paste the simplified code below it works fine. The menu can be created and deleted (via Tools Macro macros) and invoked. If I open a workbook with other routines in it, unprotect the existing VBA code, and then insert a new module in the same way, the DIM statement at the start will not compile - "User defined type not defined" The working workbook help system recognises 'CommandBarPopup' and describes the object, but the non working one gives 'keyword not found' I'd appreciate any clues. Regards Phil '--------------------------------------- Sub CreateMenu() Dim NewMenu As CommandBarPopup 'Compile error: User defined type not defined ' Delete the menu if it already exists Call DeleteMenu ' Add the menu before Help (will fail if help missing) Set HelpMenu = CommandBars(1).FindControl(ID:=30010) Set NewMenu = CommandBars(1).Controls.Add _ (Type:=msoControlPopup, _ Befo=HelpMenu.Index, _ temporary:=True) ' Add a caption for the menu NewMenu.Caption = "MyMenu" ' ADD MENU ITEM Set MenuItem = NewMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "About" .OnAction = "About" End With End Sub '----------------------------------------- Sub DeleteMenu() On Error Resume Next CommandBars(1).Controls("MyMenu").Delete End Sub '---------------------------- Sub About() MsgBox "About called" End Sub |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Sounds like the workbooks in question are missing a reference to Microsoft
Office 11.0 Object Library under Tools, References in the VBE. "Phil B" <phil.remove.brady@hotmail dot co dot united kingdom wrote in message ... I have an oddity with trying to enhance an existing workbook by adding a menu. If I open a fresh Excel 2003 workbook, open VBA, insert a module, and paste the simplified code below it works fine. The menu can be created and deleted (via Tools Macro macros) and invoked. If I open a workbook with other routines in it, unprotect the existing VBA code, and then insert a new module in the same way, the DIM statement at the start will not compile - "User defined type not defined" The working workbook help system recognises 'CommandBarPopup' and describes the object, but the non working one gives 'keyword not found' I'd appreciate any clues. Regards Phil '--------------------------------------- Sub CreateMenu() Dim NewMenu As CommandBarPopup 'Compile error: User defined type not defined ' Delete the menu if it already exists Call DeleteMenu ' Add the menu before Help (will fail if help missing) Set HelpMenu = CommandBars(1).FindControl(ID:=30010) Set NewMenu = CommandBars(1).Controls.Add _ (Type:=msoControlPopup, _ Befo=HelpMenu.Index, _ temporary:=True) ' Add a caption for the menu NewMenu.Caption = "MyMenu" ' ADD MENU ITEM Set MenuItem = NewMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "About" .OnAction = "About" End With End Sub '----------------------------------------- Sub DeleteMenu() On Error Resume Next CommandBars(1).Controls("MyMenu").Delete End Sub '---------------------------- Sub About() MsgBox "About called" End Sub |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Many thanks Jim,
that was it - ticking Office 12.0 Object Library fixed it. Great! Phil "Jim Rech" wrote in message ... Sounds like the workbooks in question are missing a reference to Microsoft Office 11.0 Object Library under Tools, References in the VBE. "Phil B" <phil.remove.brady@hotmail dot co dot united kingdom wrote in message ... I have an oddity with trying to enhance an existing workbook by adding a menu. If I open a fresh Excel 2003 workbook, open VBA, insert a module, and paste the simplified code below it works fine. The menu can be created and deleted (via Tools Macro macros) and invoked. If I open a workbook with other routines in it, unprotect the existing VBA code, and then insert a new module in the same way, the DIM statement at the start will not compile - "User defined type not defined" The working workbook help system recognises 'CommandBarPopup' and describes the object, but the non working one gives 'keyword not found' I'd appreciate any clues. Regards Phil '--------------------------------------- Sub CreateMenu() Dim NewMenu As CommandBarPopup 'Compile error: User defined type not defined ' Delete the menu if it already exists Call DeleteMenu ' Add the menu before Help (will fail if help missing) Set HelpMenu = CommandBars(1).FindControl(ID:=30010) Set NewMenu = CommandBars(1).Controls.Add _ (Type:=msoControlPopup, _ Befo=HelpMenu.Index, _ temporary:=True) ' Add a caption for the menu NewMenu.Caption = "MyMenu" ' ADD MENU ITEM Set MenuItem = NewMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "About" .OnAction = "About" End With End Sub '----------------------------------------- Sub DeleteMenu() On Error Resume Next CommandBars(1).Controls("MyMenu").Delete End Sub '---------------------------- Sub About() MsgBox "About called" End Sub |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Jim Rech explained :
Sounds like the workbooks in question are missing a reference to Microsoft Office 11.0 Object Library under Tools, References in the VBE. "Phil B" <phil.remove.brady@hotmail dot co dot united kingdom wrote in message ... I have an oddity with trying to enhance an existing workbook by adding a menu. If I open a fresh Excel 2003 workbook, open VBA, insert a module, and paste the simplified code below it works fine. The menu can be created and deleted (via Tools Macro macros) and invoked. If I open a workbook with other routines in it, unprotect the existing VBA code, and then insert a new module in the same way, the DIM statement at the start will not compile - "User defined type not defined" The working workbook help system recognises 'CommandBarPopup' and describes the object, but the non working one gives 'keyword not found' I'd appreciate any clues. Regards Phil '--------------------------------------- Sub CreateMenu() Dim NewMenu As CommandBarPopup 'Compile error: User defined type not defined ' Delete the menu if it already exists Call DeleteMenu ' Add the menu before Help (will fail if help missing) Set HelpMenu = CommandBars(1).FindControl(ID:=30010) Set NewMenu = CommandBars(1).Controls.Add _ (Type:=msoControlPopup, _ Befo=HelpMenu.Index, _ temporary:=True) ' Add a caption for the menu NewMenu.Caption = "MyMenu" ' ADD MENU ITEM Set MenuItem = NewMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "About" .OnAction = "About" End With End Sub '----------------------------------------- Sub DeleteMenu() On Error Resume Next CommandBars(1).Controls("MyMenu").Delete End Sub '---------------------------- Sub About() MsgBox "About called" End Sub since you're specifying its 'type' in the 'Add' method, you can just define it as... Dim NewMenu As CommandBarControl -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Missing CommandBarPopup when Moving Vista developed Excel Addin to XP machine | Excel Programming | |||
csv file not recognised with ms-office 2003 | Excel Programming | |||
How to trap the OnAction of a CommandBarPopup | Excel Programming | |||
Excel VBA not recognised | Excel Programming | |||
focus on commandbarPopup | Excel Programming |