Track sheets added and deleted

Question

I am working on adding excel and hit the checkpoint.

I want to update combobox with sheets in an Excel workbook. I can currently do this with a foreach loop. However, this is limited in the sense that it only picks up the added or subtracted sheets when I run it. My supplement looks sticky with the "Refresh Sheets" button, and I would like to avoid this if possible.

Ideally, I could attach the worksheet object to a drop-down sign so that it is updated on the fly without my intervention. However, my research has shown that this is not supportive. I have not tried this since I am not up to my development computer until I am, but I am almost sure, since it does not inherit IList, it will not work as a data source.

Has anyone done something like this? I would really like this work to work correctly.

Thanks!

Answer 1

The Excel.Application object has a WorkbookNewSheet event that fires when a new sheet is added. Unfortunately, it does not seem to have an event that fires when a sheet is deleted.

You can put the combobox update code in the combobox DropDown event. It starts when the combo box opens, and your code can update the elements of the combo box before the list is visible.

Answer 2

I like WarrenG's idea of ​​a dropdown event. If it works, it seems perfect. Otherwise, I would recommend using the Sheet_Activate event of the workbook. This works when sheets are created or deleted, at least if done by the user.

In C #, you need to instantiate an event and create a handler. In fact, you can do this in the Form_Load event, so creating an instance of the form creates an event handler. You need to set up links to Interop.Excel so that your Form.cs code looks something like this:

So your Form_Load event looks something like this:

 using Excel = Microsoft.Office.Interop.Excel; namespace ExcelWorkbook1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { Globals.ThisWorkbook.SheetActivate += new Excel.WorkbookEvents_SheetActivateEventHandler( ThisWorkbook_SheetActivate); } private void ThisWorkbook_SheetActivate(object Sh) { //Fill your combobox here } } } 

EDIT: I found the base code on this MSDN site .