How to allow excel data modification in manual as well as programmatically?

Question

I am using C# windows application for Excel data add/update. I had added Microsoft.Office.Interop.Excel reference(Reference -> Right Click -> Add Reference -> COM -> Type Libraries -> Microsoft Excel 1X.0 Object Libraries). On my form, I have one panel control panel1, one List-box lstSamples and two button btnAddSample, btnFormatWorksheet.

My sample code is as below:

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;


public partial class Form1 : Form
{
    Microsoft.Office.Interop.Excel.Application excelApp;
    Workbook excelWorkBook;
    Worksheet excelWorkSheet;

    public Form1()
    {
        InitializeComponent();
        LoadExcelFile();
    }

    [DllImport("user32.dll")]
    static extern IntPtr SetParent(IntPtr hWndChild, IntPtr hWndNewParent);

    private void LoadExcelFile()
    {
        excelApp = new Microsoft.Office.Interop.Excel.Application();

        excelApp.Visible = true;
        excelApp.ScreenUpdating = true;
        excelApp.EnableAutoComplete = false;
        excelWorkBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        IntPtr excelHwnd = new IntPtr(excelApp.Application.Hwnd);
        SetParent(excelHwnd, panel1.Handle);
    }

    private void btnAddSample_Click(object sender, EventArgs e)
    {
        excelWorkSheet = (Worksheet)excelWorkBook.Worksheets.get_Item(1);
        int lastUsedRow = excelWorkSheet.UsedRange.Rows.Count;
        excelWorkSheet.Cells[lastUsedRow + 1, 1] = lstSamples.SelectedItem.ToString();
        lstSamples.Items.Remove(lstSamples.SelectedItem);
    }

   private void btnFormatWorksheet_Click(object sender, EventArgs e)
   {
        Range chartRange;
        excelWorkSheet = (Worksheet)excelWorkBook.Worksheets.get_Item(1);
        chartRange = excelWorkSheet.get_Range("b2", "e9");
        chartRange.BorderAround(XlLineStyle.xlContinuous, 
        XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 
        XlColorIndex.xlColorIndexAutomatic);

    }
}    

Please follow the steps as I mentioned 1. Run the application and add data in "A1" cell (Which is of string type) 2. Again add some data in "A2" cell and Press enter 3. Select one item from lstSamples listbox and click on btnAddSample (Result is like selected item will get added into "A3" cell 4. Try to modify "A1" or "A2" cell data. (Here lstSample is having items of string type like Test1, Test2, Test3,....). If you are able to edit cells then click on btnFormatWorksheet then try to edit any cell.

Answer 1

Why are you referencing the COM DLL? You should be referencing the .Net PIA's - the Primary Interop Assemblies in this location, via the .Net tab in the References window and browse to:

C:\Program Files (x86)\Microsoft Visual Studio [version]\Visual Studio Tools for Office\PIA\Office[version]\Microsoft.Office.Interop.Excel.dll

Only for unit testing do you reference the COM one. See my answer here and how I originally worked it out. It's easy to get confused because in Solution Explorer they are both called the same thing!

---

If that doesn't work, I originally put this as an answer to save other peoples time being wasted.

Both Ahmed and I cannot reproduce the problem you described with the code you have provided.

See I typed in cells A1 and A2, then I selected an item in the list and clicked the button. Then I select the cell A2 and type Editable.

ps If you can provide the steps to reproduce I'll be happy to take another look at it.

UPDATE:

Your revised steps to reproduce the problem are incorrect, it works for me:

UPDATE 2:

Check if the message pump filters are causing the focus to go to another cell/control:

Excel CustomTaskPane with WebBrowser control - keyboard/focus issues