Excel sheet delete/rename/move/copy

Question

How do I handle following events in excel 2003 (with C#)?

1. sheet rename
2. sheet delete
3. sheet move
4. 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.

thanks...

 

 

 

Answer 1

Hi,

Is this a VSTO related or Interop related question?

Answer 2

Interop related.

Answer 3

Hi,

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?

 

Answer 4

Thanks, 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?
Thanks!!

Answer 5

Hi,

The following controls are available in the Ply commandbar:
&Ungroup Sheets
&Insert...
&Delete
&Rename
&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

 

Answer 6

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.

 

Answer 7

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
Next Ctrl

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:

ActiveWorkbook.UnProtect

Answer 8

Thanks! that worked.