PDA

View Full Version : customize right click menu on chart



grvs
11-03-2008, 08:26 AM
Hey guys!

I want to add an item when I right click on chart in excel. I could do it for a cell but unable to do it for charts.

Here is what I did for cell which worked.


'In ThisWorkBook
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Run ("DeleteCustomMenu") 'remove possible duplicates
Run ("BuildCustomMenu") 'build new menu
End Sub

'In Module
Option Explicit
Private Sub BuildCustomMenu()
Dim ctrl1 As CommandBarControl
Dim btn1 As CommandBarControl
Dim i As Integer
'add a 'popup' control to the cell commandbar (menu)
Set ctrl1 = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True)
ctrl1.Caption = "Merge Two Buckets..."
End Sub

Private Sub DeleteCustomMenu()
Dim ctrl1 As CommandBarControl
'go thru all the cell commandbar controls and delete our menu item
For Each ctrl1 In Application.CommandBars("Cell").Controls
If ctrl1.Caption = "Merge Two Buckets..." Then ctrl1.Delete
Next
End Sub

here is what I did for chart which did not work
'In ThisWorkBook
Private Sub Chart_BeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'May be this is first error "Chart_BeforeRightClick()" I really don't know what to use here
'when I used Sub Workbook_SheetBeforeRightClick() for chart, i got a menu item in the main tool bar (before File Tab)
'In excel 2007 I got a button in 'Add-ins Tab. (I tried on both 2003 and 2007 version)
Run ("DeleteCustomMenu")
Run ("BuildCustomMenu")
End Sub

'In Module
Option Explicit
Private Sub BuildCustomMenu()
Dim ctrl2 As CommandBarControl
Dim btn2 As CommandBarControl
Dim i As Integer
'add a 'popup' control to the "Chart Menu Bar" commandbar (menu)
Set ctrl2 = Application.CommandBars("Chart Menu Bar").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True) ' I tried "chart Area", "ChartArea", "Plot Area", "Chart", but nothing worked
ctrl2.Caption = "Merge Two Buckets..."
End Sub

Private Sub DeleteCustomMenu()
Dim ctrl1 As CommandBarControl
For Each ctrl2 In Application.CommandBars("Chart Menu Bar").Controls
If ctrl2.Caption = "Merge Two Buckets..." Then ctrl2.Delete
Next
End Sub


I hope u guys understand my problem. thanks...

Kenneth Hobs
11-03-2008, 10:15 AM
You need to use a Chart event, not a Sheet event. For the 2 types see http://www.computorcompanion.com/LPMArticle.asp?ID=221
Please post your final solution or post back if you need more help.

Andy Pope
11-04-2008, 01:58 AM
Hi,

Kenneth is correct in that to use the Rightclick event of the chart sheet or create a class to handle chartevents of a chartobject.

What I do not understand from your code samples is why you destroy and build the menus on every right click. Why not create the menu addition on workbook open and delete beforeclose?

grvs
11-05-2008, 01:03 AM
Hi Kenneth
The link you posted is exactly what i needed. Other than chart events I learned some other things too. But being a newbie, I could not use it properly to get the desired results.

Hey Andy
Good idea, I was using events in VBA first time, so took whatever i got from net. Thanks.

Info: I am using excel 2007

Now I am trying to use chart events but still have following problems.
Whenever I right-click a custom button appears in Add-In Menu at the top. not in right click menu
Attached screen shot here and code below

In MS Excel Objects under chart1:
Option Explicit

Private Sub Chart_BeforeRightClick(Cancel As Boolean)
Run ("DeleteCustomMenu")
Run ("BuildCustomMenu")
End Sub
In Modues Under MChartEvents
Option Explicit

Private Sub BuildCustomMenu()
Dim ctrl2 As CommandBarControl
Dim btn2 As CommandBarControl
Dim i As Integer
'add a 'popup' control to the "Chart Menu Bar" commandbar (menu)
Set ctrl2 = Application.CommandBars("Chart").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True)
ctrl2.Caption = "Merge Two Buckets..."
End Sub

Private Sub DeleteCustomMenu()
Dim ctrl2 As CommandBarControl
For Each ctrl2 In Application.CommandBars("Chart").Controls
If ctrl2.Caption = "Merge Two Buckets..." Then ctrl2.Delete
Next
End Sub
The other problem is the menu comes only for a chart for which i code.
I have like 10-200 charts in my workbook. So i want to use class module (explained in Link posted by Kenneth). but I guess i m missing something as it simply doesn't work.

here is the code which I used in class module

Option Explicit

' Declare object of type "Chart" with events
Public WithEvents EvtChart As Chart

Private Sub EvtChart_BeforeRightClick(Cancel As Boolean)
Run ("DeleteCustomMenu")
Run ("BuildCustomMenu")
End Sub
I did not do any changes to code in Module (under MChartEvents) when I used class module

Andy Pope
11-05-2008, 03:58 AM
In xl2003 this would add a menu item to the popup menu when right clicking the plot area.



Private Sub BuildCustomMenu()
Dim ctrl2 As CommandBarControl
Dim btn2 As CommandBarControl
Dim i As Integer
'add a 'popup' control to the "Chart Menu Bar" commandbar (menu)
Set ctrl2 = Application.CommandBars("Plot Area").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True)
ctrl2.Caption = "Merge Two Buckets..."
End Sub


I believe the right click menus in xl2007 are not accessible via code.


I think you need to create RibbonX code to add a group and items to the contextual chart tabs.

I have a addin that allows user to apply pattern fills to charts which has all the code and xml you should need to work through this problem
http://www.andypope.info/charts/patternfills.htm

grvs
11-05-2008, 05:35 AM
Thanks Andy, my luck that you have told me, otherwise I would have spent too much time, to see why it doesn't work.

grvs
11-10-2008, 02:01 AM
Hey Andy, tried to understand your code, but I could not understand how to make use of it in my problem.
My task is to merge two bars in a histogram on user demand. The histogram is in a chart, (there are many charts, each on one spreadsheet, with data for all charts on another (single) spread sheet.
First i thought I can add a custom right click button, saying "merge two bars". but now as we know " right click menus in xl2007 are not accessible via code", I would be happy to add a button in/above ribbon.

and I want to use class module so that my code applies on all the charts. (Is there any other way ?)

So please guide me, I don't want me you guys to write me a code but provide me a direction, so that i can learn while doing my work.

Andy Pope
11-10-2008, 03:12 AM
Try this very simple example.
The workbook contains 2 charts.

The ribbon xml adds a button to the Layout contextual menu.

The code executed when the button is pressed simply displays the name of the selected chartobject.

grvs
11-10-2008, 05:42 AM
Hey, where do I find the ribbon xml file... I don't have visual studio, i just work in VB editor that comes with excel.

Andy Pope
11-10-2008, 05:45 AM
Have a read of Ron's page on ribbon code and editing.
http://www.rondebruin.nl/ribbon.htm

I used the CustomUI editor to create the xml required.

grvs
11-11-2008, 07:00 AM
Now i have to learn xml. I will start tomorrow. But I faced a new problem.
I am writing that here as I don't know whether I should start a new thread.

I was able to add a button in ribbon under Addin Tab. But when I click on that button, it says
Cannot run macro 'WorkbookName!MacroName'. The macro may not be available in this workbook or all macros may be disabled
Now things I know that Macros are enabled and when I run it from VB Editor, it runs fine. I searched on Internet, found many people have this problem with excel 2007, but couldn't get a solution.

Here is the relevant code

In Module under MChartEvents

Option Explicit

Private Sub BuildCustomMenu()
Dim ctrl1 As CommandBarControl
Dim btn1 As CommandBarControl
Dim i As Integer

'add a 'popup' control to the "Chart Menu Bar" commandbar (In Ribbon, Under Addins)
Set ctrl1 = Application.CommandBars("Chart").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True)
ctrl1.Caption = "Merge Two Buckets"


Dim BucketsInCDB As Integer 'some variable
'add the submenus
For i = 1 To BucketsInCDB - 1 'add a few menu items
Set btn1 = ctrl1.Controls.Add
btn1.Caption = "Bin:" & i & " With Bin:" & i + 1
btn1.Tag = i
btn1.OnAction = "MergeBuckets"
'btn1.Caption = data in bucket i of CDB
Next
End Sub

Private Sub DeleteCustomMenu()
Dim ctrl1 As CommandBarControl
For Each ctrl1 In Application.CommandBars("Chart").Controls
If ctrl1.Caption = "Merge Two Buckets" Then ctrl1.Delete
Next
End Sub

Public Sub MergeBuckets()
Debug.Print "cp1"
End Sub

In Chart1, from where I call this

Private Sub Chart_BeforeRightClick(Cancel As Boolean)
Run ("DeleteCustomMenu")
Run ("BuildCustomMenu")
End Sub

Andy Pope
11-11-2008, 07:19 AM
This works for me based on your code and description.

Still not sure why you are building the menus on right-click rather than on open.

grvs
11-11-2008, 10:49 PM
Thanks Andy for taking too much trouble for me
Now I know the function now(), which works better then "cp1", "cp2" etc.

Yeah your file also worked for me..
Here is my file... this is created from another macro. This other macro reads a csv file, loads the data into a .xlsm file and generate graphs from that data.

After that I wrote my code to "merge bins" into this new file (t2.xlsm). Later on I will need something that automatically transfer code which "merge bins" to this file. Any ideas on that ?

Andy Pope
11-12-2008, 04:32 AM
Your code works if the activesheet is a worksheet but fails for chart sheet.
I spent some time trying to reason why this should be but gave up :(

If possible use a template file to create the .xlsm file. Other wise you will need to use code to populate code.

grvs
11-12-2008, 04:50 AM
Ok, i can try templates (though never used one earlier) but before that can you give me a little guidance on what are things I should use in VB, as whatever I use, seem to cause/have some problem.

I have a .csv (generated by SAS) file like attached here. I need to load it into excel, create charts for each data block (data blocks are separated by blank lines), and merge two bars user wants so.
I have done the chart generating part, but i can start from scratch again, as I hope I will be able to use code I wrote to generate charts again.


And I don't know how to thank you, you are such a helpful person.

Andy Pope
11-12-2008, 09:30 AM
You can use the macro recorder to get code for reading a csv file into a worksheet.
in xl2007 use, Data > Get External Data > From Text.

Once the data is in you can run your code for creating charts from blocks of data.