Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, January 14, 2009 5:54 PM
Hi,
How do i get List of ID numbers for built-in CommandBar controls in Excel 2007.
I saw the list for Excel 2000 at this link
http://support.microsoft.com/kb/213552
and i have tried using the same id for 2007.
I need the id for row -> right click -> delete -> Entire row.
The reason i need this is, i want to capture the event for delete and insert of rows or columns in excel.
One way i have found is capturing the ID's of these buttons and then write my own events on click of these buttons.
I wrote an event in a case where we select a row and then right click and select Delete.
private Office.CommandBar cbar;
private Office.CommandBarButton rowDeleteButton = null;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
cb = this.Application.CommandBars["Row"];
rowDeleteButton = (Office.CommandBarButton)cb.FindControl(missing, 293, missing, missing, true);
rowDeleteButton.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(rowDeleteButton_Click);
}
private void rowDeleteButton_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
{
MessageBox.Show(Ctrl.Caption + " Clicked");
}
This works fine. But i wanted to do the same thing for other way of deleting row as well which i am not getting.
I should get it by doing something like this
cb = this.Application.CommandBars[1];
cellRowDeleteButton = (Office.CommandBarButton)cb.FindControl(missing, 478, missing, missing, true);
cellRowDeleteButton.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(cellRowDeleteButton_Click);
But i guess, i am not putting the correct id for that button.
Cam someone please help me.
Please let me know if there is any other way of doing this.
Regards,
Shailendra
All replies (14)
Thursday, January 15, 2009 8:17 AM ✅Answered
Excel 2007 no longer uses the CommandBars object model for this, it uses the new Ribbon UI.
you'd have to define a Ribbion for your add-in using Ribbon XML. RibbonXML has a <commands> element in which you can list each built-in <command> item you want to repurpose. These are identified using the idMso for the command. You can discover the idMso by looking up the command in the Options/Customize lists, then hovering the mouse pointer over the command name. The idMso is the last item in the tooltip, in parentheses.Cindy Meister, VSTO/Word MVP
Thursday, January 22, 2009 1:34 PM ✅Answered | 1 vote
Hi Shailendra
OK, found some time to dig into this (Excel isn't "my" application, so I need to experiment). This appears to work
| public void myRowInsert(Office.IRibbonControl control, bool cancelDefault) |
| { |
| Excel.Application app = Globals.ThisAddIn.Application; |
| Excel.Range rng = app.ActiveWindow.RangeSelection; |
| rng.Rows.Insert(Excel.XlDirection.xlDown, System.Type.Missing); |
| //MessageBox.Show(cancelDefault.ToString()); |
| cancelDefault = true; //this is the default, so probably not necessary |
| } |
Cindy Meister, VSTO/Word MVP
Tuesday, January 27, 2009 2:34 PM ✅Answered
Thanks Cindy ...
I figured it out. This is what i am doing now.
| public void myRowInsert(Office.IRibbonControl control, bool cancelDefault) |
| { |
| Globals.ThisAddIn.rowInsert = true; |
| Excel.Application app = Globals.ThisAddIn.Application; |
| Excel.Range rng = app.ActiveWindow.RangeSelection; |
| int targetCount = rng.Cells.Count; |
| if (!((targetCount % ThisAddIn.COLCOUNT) == 0)) |
| { |
| rng.EntireRow.Select(); |
| } |
| rng = app.ActiveWindow.RangeSelection; |
| cancelDefault = true; |
| rng.Rows.Insert(Excel.XlDirection.xlDown, System.Type.Missing); |
| } |
So incase a cell is selected and then user tries to Insert a row, i am selecting the whole row through code and then using the code you suggested and it works fine.
Regards,
Shailendra
Thursday, January 15, 2009 4:09 PM
Hi,
Can you please point to an example of doing this.
Regards,
Shailendra
Thursday, January 15, 2009 4:15 PM
Hi,
Also you can download the ribbon control ids from here
HTH
Jaz
Thursday, January 15, 2009 9:26 PM
Hi Cindy,
I have tried the way you have mentioned by using Ribbon XML.
I have used the following XML
<commands>
<command idMso="SheetRowsInsert" onAction="myRowInsert" />
</commands>
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabAddIns">
<group id="MyGroup" label="My Group"></group>
</tab>
</tabs>
</ribbon>
And then put the following method in the code behind
public void myRowInsert(Office.IRibbonControl control, bool cancelDefault)
{
MessageBox.Show("Inserting Row");
}
And when i click the menu option Insert -> Insert Sheet Rows
I do get the message box, but then the control is not given to the SheetChange Event and the row does not get deleted.
This was not happening prior to me adding this event.
Can you please tell me as to what should i do to give the control back to SheetChange event.
Regards,
Shailendra
Friday, January 16, 2009 8:06 AM
Just remove this line:
<command idMso="SheetRowsInsert" onAction="myRowInsert" />
you have overridden the default behavior for the SheetRowsInsert Command.
What actually are you trying to do?
Greets- HelmutHelmut Obertanner [http://www.x4u.de] [http://www.outlooksharp.de]
Friday, January 16, 2009 8:23 AM
Shailendra_G said:
...And then put the following method in the code behind
public void myRowInsert(Office.IRibbonControl control, bool cancelDefault)
{
MessageBox.Show("Inserting Row");
}
And when i click the menu option Insert -> Insert Sheet Rows
I do get the message box, but then the control is not given to the SheetChange Event and the row does not get deleted.
This was not happening prior to me adding this event.
Can you please tell me as to what should i do to give the control back to SheetChange event.
The parameter cancelDefault will let you allow, or suppress, the button's default actions. If you set cancelDefault to False, then Excel should continue as it usually does. However, then one has to ask why you need to trap the button at all?
If you want something specific other than the default action to happen when the user clicks the button, then you need to put (or call) that code from the onAction method. This is the "Click event" for the button.Cindy Meister, VSTO/Word MVP
Friday, January 16, 2009 2:55 PM
Hi Cindy,
My actual problem is that, i want to know if a user is deleting/Inserting rows/columns.
So since there are many ways for user to do this, i am trying to set a flag on every such event.
Then in the Application.SheetChange event, i will check the flag, through which i will know if a row/column has been
deleted/Inserted. Then i will take corresponding action.
I have tried setting cancelDefault to False. But still the code flow stops at the method myRowInsert. It does not hit Application.SheetChange event when the row is inserted.
Regards,
Shailendra
Tuesday, January 20, 2009 3:30 PM
Shailendra_G said:
My actual problem is that, i want to know if a user is deleting/Inserting rows/columns.
So since there are many ways for user to do this, i am trying to set a flag on every such event.
Then in the Application.SheetChange event, i will check the flag, through which i will know if a row/column has been
deleted/Inserted. Then i will take corresponding action.
I have tried setting cancelDefault to False. But still the code flow stops at the method myRowInsert. It does not hit Application.SheetChange event when the row is inserted.
But the row is inserted?
Cindy Meister, VSTO/Word MVP
Tuesday, January 20, 2009 3:59 PM
No, the row does not get inserted.
Shailendra
Tuesday, January 20, 2009 4:15 PM
Shailendra_G said:
No, the row does not get inserted.
Mmm. How about if you cancelDefault = true. (I'd have thought false should let the default procede, but maybe it's a backwards logic. I don't have time to test it, at the moment.)
The row needs to be inserted in order to trigger the event, that's logical. Cindy Meister, VSTO/Word MVP
Tuesday, January 20, 2009 5:48 PM
Hi Cindy,
I tried that too ... but the result is the same.
The row does not get inserted. I wonder why?
Shailendra
Friday, January 23, 2009 7:18 PM
Thanks Cindy .... that was helpful.
But now i tried adding more functionality like Insert Column, Row Delete and Column Delete.
I have the xml in my Ribbon in the following way
| <commands> |
| <command idMso="SheetRowsInsert" onAction="myRowInsert" /> |
| <command idMso="SheetColumnsInsert" onAction="myColumnInsert" /> |
| <command idMso="SheetRowsDelete" onAction="myRowDelete" /> |
| <command idMso="SheetColumnsDelete" onAction="myColDelete" /> |
| </commands> |
I have defined functions in the following way
| public void myRowInsert(Office.IRibbonControl control, bool cancelDefault) |
| { |
| Excel.Application app = Globals.ThisAddIn.Application; |
| Excel.Range rng = app.ActiveWindow.RangeSelection; |
| rng.Rows.Insert(Excel.XlDirection.xlDown, System.Type.Missing); |
| cancelDefault = true; //this is the default, so probably not necessary |
| } |
| public void myColumnInsert(Office.IRibbonControl control, bool cancelDefault) |
| { |
| Globals.ThisAddIn.colInsert = true; |
| Excel.Application app = Globals.ThisAddIn.Application; |
| Excel.Range rng = app.ActiveWindow.RangeSelection; |
| rng.Columns.Insert(Excel.XlDirection.xlToRight, System.Type.Missing); |
| cancelDefault = true; //this is the default, so probably not necessary |
| } |
But, whats happening is that if i dont define any other methods apart from myRowInsert(), then the row insert works fine. But if i define any other method like myColumnInsert(), the code inside the method does get executed, and also control goes to Sheet Change event, but the sheet does not change, i.e the column does not get inserted.
I am trying to find if i can tweak the code any more, but can you please let me know if i am doing anything wrong.
I have noticed one more thing. If i am selecting the complete row or column and then clicking on the menu option (Insert Sheet Rows or Insert Sheet Columns), then it is working fine. But if i am selecting only a cell and then clikcing on the menu option, thats when the events are fired, but the rows or columns are not inserted.
Thanks,
Shailendra