I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.

Thanks,
Fred

Maybe qualifying it would help:

Dim xxx as application.commandbarcontrol



Fred Davis wrote:

I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.

Thanks,
Fred

--

Dave Peterson

CommandBars are part of the Office object library, I believe it would
be

Dim xxx As Office.CommandBarControl

HTH,
JP


On Mar 3, 5:03*pm, Dave Peterson wrote:
Maybe qualifying it would help:

Dim xxx as application.commandbarcontrol





Fred Davis wrote:

I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. *However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.

Thanks for the correction.

JP wrote:

CommandBars are part of the Office object library, I believe it would
be

Dim xxx As Office.CommandBarControl

HTH,
JP

On Mar 3, 5:03 pm, Dave Peterson wrote:
Maybe qualifying it would help:

Dim xxx as application.commandbarcontrol





Fred Davis wrote:

I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.


--

Dave Peterson

Hi Dave (and JP),

Thank you both for your suggestions, but neither 'application.' nor
'office.' makes any difference.

What I really do not understand is the code works in its own spreadsheet,
but fails with a compile error when I put it in my existing spreadsheet.
Could it be that my spreadsheet was created in an older version of Excel and
is missing some add-in or other?

By the way, the code I have downloaded comes from the file
'AddingCustomMenus.zip'. on Ozgrid

Very confused,
Fred


"Dave Peterson" wrote:

Maybe qualifying it would help:

Dim xxx as application.commandbarcontrol



Fred Davis wrote:

I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.

Thanks,
Fred

--

Dave Peterson

Hi guys,

I have just created a brand new spreadsheet and cut and pasted the offending
code into it. Guess what - it works!

So, what is missing from my old spreadsheet that is stopping the code
working?

Re-building the old spreadsheet under my current version is definitely a
'LAST RESORT'. I don't want to even think about it!

Fred

"Fred Davis" wrote:

Hi Dave (and JP),

Thank you both for your suggestions, but neither 'application.' nor
'office.' makes any difference.

What I really do not understand is the code works in its own spreadsheet,
but fails with a compile error when I put it in my existing spreadsheet.
Could it be that my spreadsheet was created in an older version of Excel and
is missing some add-in or other?

By the way, the code I have downloaded comes from the file
'AddingCustomMenus.zip'. on Ozgrid

Very confused,
Fred


"Dave Peterson" wrote:

Maybe qualifying it would help:

Dim xxx as application.commandbarcontrol



Fred Davis wrote:

I want to embed a custom menu in an old (working) spreadsheet.

I have downloaded a number of pre-written solutons which all work when used
in the original spreadsheet downloaded. However, when I cut and paste the
code into my spreadsheet I get the compiler error "User-defined type not
defined" on the statement _dim xxx as CommandBarControl_.

WHY?

If it works on its own, why won't it work in my spreadsheet?

I have been struggling with this for too many hours now so any help will be
greatly appreciated.

Thanks,
Fred

--

Dave Peterson

Can you post the code?

--JP

On Mar 4, 5:12*am, Fred Davis
wrote:
Hi guys,

I have just created a brand new spreadsheet and cut and pasted the offending
code into it. *Guess what - it works!

So, *what is missing from my old spreadsheet that is stopping the code
working?

Re-building the old spreadsheet under my current version is definitely a
'LAST RESORT'. *I don't want to even think about it!

Fred

Hi JP,

I don't know if it's possible to attach files to these posts so here is the
code lonhand:



Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Befo=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.


'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&xt Menu"

'Add a contol to the sub menu, just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = "MyMacro2"
End With



End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
End Sub

Thanks for your interest.
Fred

"JP" wrote:

Can you post the code?

--JP

On Mar 4, 5:12 am, Fred Davis
wrote:
Hi guys,

I have just created a brand new spreadsheet and cut and pasted the offending
code into it. Guess what - it works!

So, what is missing from my old spreadsheet that is stopping the code
working?

Re-building the old spreadsheet under my current version is definitely a
'LAST RESORT'. I don't want to even think about it!

Fred