CommandBarControl object
 

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

CommandBarControl object
 

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

CommandBarControl object
 

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.

CommandBarControl object
 

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

CommandBarControl object
 

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

CommandBarControl object
 

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

CommandBarControl object
 

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

CommandBarControl object
 

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

CommandBarControl object
 

I'd guess you're missing a reference to "Microsoft Office XX Object Library"
under Tools, References in the VBE.

--
Jim
"Fred Davis" wrote in message
...
| 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
|
|
|

CommandBarControl object
 

Hi Jim, or should I say 'Superstar',

You're spot on. The troulble is _References_ is greyed out. Where do I hit
it?

Fred

"Jim Rech" wrote:

I'd guess you're missing a reference to "Microsoft Office XX Object Library"
under Tools, References in the VBE.

--
Jim
"Fred Davis" wrote in message
...
| 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
|
|
|

CommandBarControl object
 

If you're running the code, stop it and try it again.

Make sure you select your project first.

Fred Davis wrote:

Hi Jim, or should I say 'Superstar',

You're spot on. The troulble is _References_ is greyed out. Where do I hit
it?

Fred

"Jim Rech" wrote:

I'd guess you're missing a reference to "Microsoft Office XX Object Library"
under Tools, References in the VBE.

--
Jim
"Fred Davis" wrote in message
...
| 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
|
|
|




--

Dave Peterson

CommandBarControl object
 

Hi Jim and Dave,

Thank you for taking the time to help me out, I really appreciate it.

Fred
PS Jim, I've been watching you help people since your days with Lotus
Symphony. Do you have a proper job? (grin)

"Jim Rech" wrote:

I'd guess you're missing a reference to "Microsoft Office XX Object Library"
under Tools, References in the VBE.

--
Jim
"Fred Davis" wrote in message
...
| 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
|
|
|

CommandBarControl object
 

your days with Lotus Symphony

Good memory, Fred. I barely remember those days!<g

--
Jim
"Fred Davis" wrote in message
...
| Hi Jim and Dave,
|
| Thank you for taking the time to help me out, I really appreciate it.
|
| Fred
| PS Jim, I've been watching you help people since your days with Lotus
| Symphony. Do you have a proper job? (grin)
|
| "Jim Rech" wrote:
|
| I'd guess you're missing a reference to "Microsoft Office XX Object
Library"
| under Tools, References in the VBE.
|
| --
| Jim
| "Fred Davis" wrote in message
| ...
| | 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
| |
| |
| |
|
|
|

CommandBarControl object
 

Fred, did you set the reference like Jim mentioned? If so, is it still
not working?


Thx,
JP

On Mar 4, 7:39*am, Fred Davis
wrote:
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
--snip--


Thanks for your interest.
Fred

CommandBarControl object
 

Hi JP,

I have now set the reference that Jim referred to and everything is working.

Thanks guys,
Fred

"JP" wrote:

Fred, did you set the reference like Jim mentioned? If so, is it still
not working?


Thx,
JP

On Mar 4, 7:39 am, Fred Davis
wrote:
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
--snip--


Thanks for your interest.
Fred