CommandBar Item for Chart Menu

[pcsmurf]

I have written a small piece of VBA code which adds a new command bar option to the main Excel menu bar. This works well and appears on the menu as expected.

Unfortunately one of the options is related to copying charts and as soon as I click on a chart in the Excel file this new menu bar gets hidden.

I'm assuming this is something to do with my new menu being added to the Standard bar and therefore not available to the Chart Menu bar but I can't figure how to get my menu bar to show on the Chart menu.

 

[TonyJollans]

Just add it to the "Chart Menu Bar" as well, in the same way you add it to the "Worksheet Menu Bar"

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[pcsmurf]

The code I use is

Sub MakeMenu()
Dim cControl As CommandBarControl
RemoveMenu 'Prevents duplicate entry of the menu item
Set cControl = Application.CommandBars(1).FindControl(ID:=30007).Controls.Add _
(Type:=msoControlButton, temporary:=True)
With cControl
.Caption = "&GifMaker"
.OnAction = "Create_Gif_File"
End With
End Sub


I'm assuming the CommandBars(1). is the reference to the standard bar, if so how do I reference the chart menu bar ?

I can't find any info on this, so I'm either looking in the wrong place or totally off-track

 

[SkipVought]

Hi,

Turn on your Macro recorder and active a command bar.

Observe the results.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[TonyJollans]

Naming and terminology can be difficult!

Commandbars(1) is the Worksheet Menu Bar - not the Standard toolbar.
If it helps, Commandbars(2) is the Chart Menu Bar.

You won't find much information on the numbers because they are not absolutely guaranteed to refer to particular toolbars. In practice, however, they are fairly safe to use and in many circumstances I would advocate using them over the names as they are language-independent. If you are working in an entirely English environment, however, your code would be easier to understand if you used Commandbars("Worksheet Menu Bar") and Commandbars("Chart Menu Bar")

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[pcsmurf]

Thanks for all the help.

The "Chart Menu Bar" solution works.

The only real problem is that it appears to be guesswork trying to find out the name of the command bar. Originally I was using "Chart".

Incidentally, the macro record tip returned the name "Chart" so that threw me a bit.

Thanks again.

 

[combo]

You can list all commandbars, they have both Name and NameLocal properties, so reference by Name is stable:

For Each cb In Application.CommandBars
Debug.Print cb.Index, cb.Name, cb.NameLocal
Next cb

combo

 

[Loomah]

You can also go a stage further identifying control IDs by using the code in this FAQ
faq707-4727

This is something I'm sure I was going to update following comments from Mr Jollans but never got round to it!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Hi Loomah!

That was a long time ago - I'm sure I can't remember what I said

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[