none
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...

     

     

     

    Monday, March 05, 2007 11:14 PM

Answers

  • 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

    Thursday, March 08, 2007 10:21 PM

All replies

  • Hi,

    Is this a VSTO related or Interop related question?

    Tuesday, March 06, 2007 7:13 PM
  • Interop related.
    Tuesday, March 06, 2007 9:17 PM
  • 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?

     

    Tuesday, March 06, 2007 10:42 PM
  • 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!!

    Thursday, March 08, 2007 1:05 AM
  • 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

     

    Thursday, March 08, 2007 1:59 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 9:12 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
    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

    Thursday, March 08, 2007 10:21 PM
  • Thanks! that worked.
    Thursday, March 08, 2007 11:56 PM