CommandBarPopup not recognised as a keyword Excel 2003
 

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

CommandBarPopup not recognised as a keyword Excel 2003
 

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

CommandBarPopup not recognised as a keyword Excel 2003
 

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

CommandBarPopup not recognised as a keyword Excel 2003
 

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