Monday, March 05, 2007 11:14 PM
How do I handle following events in excel 2003 (with C#)?
- sheet rename
- sheet delete
- sheet move
- sheet copy
Also, is it possible to disable only a few options in sheet right click popup menu? Protecting the workbook is not acceptable in my case.
Tuesday, March 06, 2007 7:13 PM
Is this a VSTO related or Interop related question?
Tuesday, March 06, 2007 9:17 PMInterop related.
Tuesday, March 06, 2007 10:42 PM
As You problably already have concluded there exist no natural events to hook these events to.
1. Sheet rename - No reliable solution that I'm aware of.
2. Sheet delete - You need to combine Workbook_SheetActivate and Sub Workbook_SheetDeactivate
3. Sheet move - As each sheet's index changes depending on its position in the worksheet collection it will require a lot of code to keep track for it.
4. Sheet copy - No reliable solution that I'm aware of.
Does the popup menu question refer to Excel 2003 or Excel 2007?
Thursday, March 08, 2007 1:05 AMThanks, Dennis.
The popup menu question is also for excel 2003. I got a solution working for popup, which is to use the "ply" commandbar and disable the commandbarcontrols that I don't want to enable.
I also found a solution to disable Edit-->Delete Sheet, and Edit--> Move or Copy Sheet. However, how do I disable a sub menu option 2 levels down? For example, I want to disable Format --> Sheet --> Rename.
In C#, following won't compile:
excelApp.CommandBars["F&ormat"].Controls["S&heet"].Controls["Rename"].Enabled = false.
What is the correct way to do that?
Thursday, March 08, 2007 1:59 PM
The following controls are available in the Ply commandbar:
&Move or Copy...
&Select All Sheets
In order to disable a menu item we need to include the "&":
Application.CommandBars("Ply").Controls("&Rename").Enabled = False
Thursday, March 08, 2007 9:12 PM
I would like to disable the rename option from Format-->Sheet menu as well. Disabling the right click popup menu ("Ply") doesn't disable it from the worksheet top menu.
Thursday, March 08, 2007 10:21 PM
OK, then the following take snippet VBA code will take care of it:
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=889)
Ctrl.Enabled = False
However, it will not prevent the end users to double click on the sheet tabs to rename. In order to fully prevent it will require a protection of the workbook. The following snippet VBA code do it:
ActiveWorkbook.Protect "", Structure:=True, Windows:=False
To restore it:
Thursday, March 08, 2007 11:56 PMThanks! that worked.